openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From MiƂosz Tylenda <mtyle...@o2.pl>
Subject Re: Subqueries in JPQL
Date Tue, 07 Oct 2008 17:21:11 GMT
Hello!

I am afraid you can't do that in a single JPQL statement - currently JPQL allows subqueries
only in WHERE and HAVING clauses. Maybe we will get something more in JPA 2.0...

Regards,
Milosz


> Hello, 
> 
> I am looking for some assistance in converting a pretty complex SQL
> statement involving subqueries to JPQL. I am looking to use a constructor
> statement so I can return multiple values (essentially most of an entity
> plus a few values), but am at a loss as to how to connect the pieces.
> 
> 
> SELECT NEW entity.helper.LeftConfList(conf.title, 
> 	COUNT(cnr) >, COUNT(cn) >)
> 
> 				>
> 
> 	FROM Conference conf, 
> 	WHERE conf.courseSectionUID = ?1 
> 	AND conf.deleted <> 1 ORDER BY conf.displaySequence
> 
> 
> The original (working) SQL is:
> 
> SELECT conf.CONFERENCE_TITLE, (SELECT COUNT(*) FROM CONFERENCE_NOTES cn,
> LAST_EDIT le
> WHERE cn.U_OBJECT_ID NOT IN (
> SELECT cn.U_OBJECT_ID 
> FROM CONFERENCE_NOTES cn, UNREAD ur
> WHERE cn.U_OBJECT_ID = ur.READ_OBJECT_ID 
> AND cn.U_CONFERENCE_ID = conf.U_OBJECT_ID
> AND ur.U_USER_ID = ?1)
> AND cn.U_CONFERENCE_ID = conf.U_OBJECT_ID
> AND le.DELETED <> 1 AND cn.U_OBJECT_ID = le.U_OBJECT_ID) AS unread_count, 
> 
> (SELECT COUNT (*) 
> FROM CONFERENCE_NOTES cn, LAST_EDIT le
> WHERE cn.U_CONFERENCE_ID = conf.U_OBJECT_ID
> AND le.DELETED <> 1 AND cn.U_OBJECT_ID = le.U_OBJECT_ID) AS total_count
> 
> FROM CONFERENCES conf, LAST_EDIT le
> WHERE conf.U_COURSE_SECTION_ID = ?2
> AND le.DELETED <> 1 AND conf.U_OBJECT_ID = le.U_OBJECT_ID ORDER BY
> conf.DISPLAY_SEQUENCE
> 
> Any help would be greatly appreciated.
> 
> Thanks.
> 
> 
> -- 
> View this message in context: http://n2.nabble.com/Subqueries-in-JPQL-tp1301743p1301743.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
> 
> 

Mime
View raw message