Re: [eclipselink-users] using IN with a subquery

[James Sutherland <jamesssss@xxxxxxxxx>]

I don't think you are allowed in JPQL to use an IN with an entity reference. 
i.e. how would this work if the Account had a composite primary key?

Try,

select t from Task t where t.user = :user or t.account.id in (select a.id
from 
User u join u.accounts a where u = :user)

or,

select t from Task t where t.user = :user or exists (select a from 
User u join u.accounts a where u = :user and a = t.account)

or,

select t from Task t, User u join u.accounts a where t.user = :user or
t.account = a and u = :user)

I would be useful to allow for IN with entities, though, so please log an
enhancement for this and vote for it.  It should be possible for at least
singleton primary keys, and composite for databases that support arrayed
ins.


Derek Knapp-3 wrote:
> 
> I have 3 entities, User, Account, and Task
> 
> this is my query
> 
> select t from Task t where t.user = :user or t.account in (select a from 
> User u join u.accounts a where u = :user)
> 
> here is the SQL generated
> 
> SELECT t0.`id`, t0.`complete_date`, t0.`create_date`, t0.`due_date`, 
> t0.`task_status`, t0.`uuid`, t0.`userid`, t0.`contactid`, t0.`noteid`, 
> t0.`subuserid` FROM `task` t0, `users` t1 WHERE (((t0.`subuserid` = ?) 
> OR  IN (SELECT DISTINCT t2.`userid`, t2.`adminlevel`, t2.`cellno`, 
> t2.`city`, t2.`cityid`, t2.`country`, t2.`disclaimer`, t2.`email`, 
> t2.`firstname`, t2.`lastname`, t2.`managerid`, t2.`office`, 
> t2.`officeid`, t2.`password`, t2.`phoneno`, t2.`region`, t2.`regionid`, 
> t2.`signatureline`, t2.`signaturestatus`, t2.`team`, t2.`teamid`, 
> t2.`title`, t2.`companyid` FROM `subuseraccess` t4, `subusers` t3, 
> `users` t2 WHERE ((? = t3.`subuserid`) AND ((t4.`subuserid` = 
> t3.`subuserid`) AND (t2.`userid` = t4.`userid`))))) AND (t1.`userid` = 
> t0.`userid`))
>      bind => [6, 6]
> 
> the JPA query seems to be fine, but the SQL it generates is not.. here 
> is the mysql error
> 
> You have an error in your SQL syntax; check the manual that corresponds 
> to your MySQL server version for the right syntax to use near 'IN 
> (SELECT DISTINCT t2.`userid`, t2.`adminlevel`, t2.`cellno`, t2.`city`, 
> t2.`ci' at line 1
> 
> 
> 
> also, why does it include the users table (t1)..  this is what I would 
> HOPE it generated
> 
> SELECT t0.`id`, t0.`complete_date`, t0.`create_date`, t0.`due_date`, 
> t0.`task_status`, t0.`uuid`, t0.`userid`, t0.`contactid`, t0.`noteid`, 
> t0.`subuserid` FROM `task` t0 WHERE (((t0.`subuserid` = 6) OR 
> t0.`userid` IN (SELECT DISTINCT t2.`userid` FROM `subuseraccess` t4, 
> `subusers` t3, `users` t2 WHERE ((6 = t3.`subuserid`) AND 
> ((t4.`subuserid` = t3.`subuserid`) AND (t2.`userid` = t4.`userid`))))))
>      bind => [6, 6]
> 
> 


-----
http://wiki.eclipse.org/User:James.sutherland.oracle.com James Sutherland 
http://www.eclipse.org/eclipselink/
 EclipseLink ,  http://www.oracle.com/technology/products/ias/toplink/
TopLink 
Wiki:  http://wiki.eclipse.org/EclipseLink EclipseLink , 
http://wiki.oracle.com/page/TopLink TopLink 
Forums:  http://forums.oracle.com/forums/forum.jspa?forumID=48 TopLink , 
http://www.nabble.com/EclipseLink-f26430.html EclipseLink 
Book:  http://en.wikibooks.org/wiki/Java_Persistence Java Persistence 
Blog:  http://java-persistence-performance.blogspot.com/ Java Persistence
Performance 
-- 
View this message in context: http://old.nabble.com/using-IN-with-a-subquery-tp31658886p31708436.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.