Hi, Also, you might consider if you really want to put a query like that in the application. Given the performace penalty (depending on your number of users, concurrency, etc.), you might want to put it in the database as a stored procedure or something. Just something to think about. Having said that; to be able to construct that query in to JPQL (considering that SQL is set-based and JPQL is object-based), I would suggest deconstructing/"reverse-engineering" it by writing it out what it should do (the requirements so to say) (with emphasis on objects & their actions [LastEdit, Conferences, Unread, etc.]). and model those objects & go from there.
regards, Judes Tumuhairwe On Tue, Oct 7, 2008 at 1:21 PM, Miłosz Tylenda <[EMAIL PROTECTED]> wrote: > 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. > > > > >
