openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Judes Tumuhairwe" <judethecuted...@gmail.com>
Subject Re: Subqueries in JPQL
Date Tue, 07 Oct 2008 19:34:03 GMT
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 <mtylenda@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.
> >
> >
>
Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message