openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From WPrecht <wpre...@umuc.edu>
Subject Subqueries in JPQL
Date Mon, 06 Oct 2008 19:38:48 GMT

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) <<result of subquery1>>, COUNT(cn) <<result of subquery2>>)

				<< Subqueries go here? >>

	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