You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by WPrecht <wp...@umuc.edu> on 2008/10/06 21:38:48 UTC
Subqueries in JPQL
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.
Re: Subqueries in JPQL
Posted by Jeremy Bauer <te...@gmail.com>.
Another option to consider is to create a view in the database which
looks similar to your original SQL and then map that view to an entity
or composition of entities. If your object model is fixed that may
not be an option, though.
-Jeremy
Re: Subqueries in JPQL
Posted by KavithaS <ka...@gmail.com>.
Hi ,
I have the same requirement Please let me know the solution.
Thank you,
Kavitha.
--
View this message in context: http://openjpa.208410.n2.nabble.com/Subqueries-in-JPQL-tp1301743p7473405.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: Subqueries in JPQL
Posted by WPrecht <wp...@umuc.edu>.
Judes Tumuhairwe wrote:
>
> 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.
>
I have been given an application to tune by a PM who's basically against
stored procedures and sweating a deadline, so I don't have the latitude to
make major code changes. Currently, one action in the app makes 2n+1 trips
to the database to build up a list of data. One to get the list of
conferences, one per conference to get the total note count and one per to
get the unread note count. Needless to say, that makes the DBA's head spin.
All three of those queries are fairly straightforward JPQL already and work
fine, unless the dataset starts to get large. I would like to get them all
into one query; I know it's really the same number of selects on the data,
but at least there is less overhead.
For instance the outer query looks like:
SELECT conf FROM Conference conf
WHERE conf.sectionId = ?1 AND conf.deleted <> 1
ORDER BY conf.displaySeq
One of the inners looks like:
SELECT COUNT(cn) FROM ConferenceNotes cn
WHERE cn.confId = ?1 AND cn.DELETED <> 1
The other is pretty similar. That Last_Edit table is joined to both the
Conference entity and the ConferenceNote which makes the JPQL syntax a lot
cleaner.
Thanks for the input guys! I might play with this some more and see if I
can restructure it and come at it from another angle.
Wayne.
--
View this message in context: http://n2.nabble.com/Subqueries-in-JPQL-tp1301743p1305240.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: Subqueries in JPQL
Posted by Judes Tumuhairwe <ju...@gmail.com>.
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 <mt...@o2.pl> 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.
> >
> >
>
Re: Subqueries in JPQL
Posted by Miłosz Tylenda <mt...@o2.pl>.
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.
>
>