You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Kevin Menard <km...@servprise.com> on 2008/02/21 02:05:59 UTC
Queries based on relationship size
Greetings,
I'm running into a little difficulty performing a query that I think
comes up often enough that it should be fairly straightforward.
I have a schema similar to the following:
Customer <--- BillingInfo <--- Order
I've mapped the read-only relationship "orderHistory" from Customer to
Order.
Now I'd like to perform two queries. One for customers with orders and
one for customers without. I'd rather not filter in the app itself
because I'd like to paginate the result lists.
Is there a way to do this without resulting to an SQLTemplate query?
Thanks,
Kevin
--
Kevin Menard
Servprise International, Inc.
Remote reboot & power control for your network
www.servprise.com +1 508.892.3823 x308
RE: Queries based on relationship size
Posted by Kevin Menard <km...@servprise.com>.
Yeah. It shouldn't be too bad. What I have now is a generic list
entities page in Tapestry. I start off with a SelectQuery which gets
the class name using a template method (boy, type erasure sure is fun),
and then subclasses can sort the values or filter out as need be.
It'd actually be really nifty if there was some sort of EJBQLExpression
that could be applied to SelectQuery. Well, I'll solve today's problem
and then look at what can be done across the framework.
Thanks again,
Kevin
--
Kevin Menard
Servprise International, Inc.
Remote reboot & power control for your network
www.servprise.com +1 508.892.3823 x308
> -----Original Message-----
> From: Andrus Adamchik [mailto:andrus@objectstyle.org]
> Sent: Thursday, February 21, 2008 2:52 PM
> To: user@cayenne.apache.org
> Subject: Re: Queries based on relationship size
>
> I hope the difference between the two will not be that noticeable (I'd
> say logically they are the same, only the assembly API is different)
> once we implement a master generics-friendly object query that was
> discussed on the dev list some time back... But this won't happen
> overnight of course...
>
> Andrus
>
>
> On Feb 21, 2008, at 9:40 PM, Kevin Menard wrote:
>
> > Thanks. I'll have to give that a go over the weekend.
> Unfortunately,
> > my API structure is centered around SelectQuery and a visitor for
> > adding
> > expressions and orderings. Changing the root query type will
> > require a
> > few more changes than I was hoping for. At least it'll give me an
> > excuse to finally play with EJB QL.
> >
> > --
> > Kevin Menard
> > Servprise International, Inc.
> > Remote reboot & power control for your network
> > www.servprise.com +1 508.892.3823 x308
> >
> >
> >> -----Original Message-----
> >> From: Andrus Adamchik [mailto:andrus@objectstyle.org]
> >> Sent: Thursday, February 21, 2008 8:56 AM
> >> To: user@cayenne.apache.org
> >> Subject: Re: Queries based on relationship size
> >>
> >> EJBQL query with outer joins should help:
> >>
> >> 1. select c from Customer c JOIN c.orderHistory
> >>
> >> (inner join filters customers with Orders, so this is like a
> >> regular SelectQuery)
> >>
> >> 2. select c from Customer c LEFT JOIN c.orderHistory d WHERE d is
> >> null
> >>
> >> (outer join combined with the WHERE clause should allow fetching
> >> customers with no orders)
> >>
> >> Two caveats: we still need to add pagination support per CAY-963; I
> >> may have taken a few _temporary_ shortcuts in the EJBQL parser that
> >> may cause trouble with flattened relationships, so let me know if
> you
> >> run into problems.
> >>
> >> Andrus
> >>
> >>
> >>
> >> On Feb 21, 2008, at 3:05 AM, Kevin Menard wrote:
> >>
> >>> Greetings,
> >>>
> >>> I'm running into a little difficulty performing a query that I
> think
> >>> comes up often enough that it should be fairly straightforward.
> >>>
> >>> I have a schema similar to the following:
> >>>
> >>> Customer <--- BillingInfo <--- Order
> >>>
> >>> I've mapped the read-only relationship "orderHistory" from
Customer
> >> to
> >>> Order.
> >>>
> >>> Now I'd like to perform two queries. One for customers with
orders
> >>> and
> >>> one for customers without. I'd rather not filter in the app
itself
> >>> because I'd like to paginate the result lists.
> >>>
> >>> Is there a way to do this without resulting to an SQLTemplate
> query?
> >>>
> >>> Thanks,
> >>> Kevin
> >>>
> >>> --
> >>> Kevin Menard
> >>> Servprise International, Inc.
> >>> Remote reboot & power control for your network
> >>> www.servprise.com +1 508.892.3823 x308
> >>>
> >>>
> >>>
> >
> >
Re: Queries based on relationship size
Posted by Andrus Adamchik <an...@objectstyle.org>.
I hope the difference between the two will not be that noticeable (I'd
say logically they are the same, only the assembly API is different)
once we implement a master generics-friendly object query that was
discussed on the dev list some time back... But this won't happen
overnight of course...
Andrus
On Feb 21, 2008, at 9:40 PM, Kevin Menard wrote:
> Thanks. I'll have to give that a go over the weekend. Unfortunately,
> my API structure is centered around SelectQuery and a visitor for
> adding
> expressions and orderings. Changing the root query type will
> require a
> few more changes than I was hoping for. At least it'll give me an
> excuse to finally play with EJB QL.
>
> --
> Kevin Menard
> Servprise International, Inc.
> Remote reboot & power control for your network
> www.servprise.com +1 508.892.3823 x308
>
>
>> -----Original Message-----
>> From: Andrus Adamchik [mailto:andrus@objectstyle.org]
>> Sent: Thursday, February 21, 2008 8:56 AM
>> To: user@cayenne.apache.org
>> Subject: Re: Queries based on relationship size
>>
>> EJBQL query with outer joins should help:
>>
>> 1. select c from Customer c JOIN c.orderHistory
>>
>> (inner join filters customers with Orders, so this is like a
>> regular SelectQuery)
>>
>> 2. select c from Customer c LEFT JOIN c.orderHistory d WHERE d is
>> null
>>
>> (outer join combined with the WHERE clause should allow fetching
>> customers with no orders)
>>
>> Two caveats: we still need to add pagination support per CAY-963; I
>> may have taken a few _temporary_ shortcuts in the EJBQL parser that
>> may cause trouble with flattened relationships, so let me know if you
>> run into problems.
>>
>> Andrus
>>
>>
>>
>> On Feb 21, 2008, at 3:05 AM, Kevin Menard wrote:
>>
>>> Greetings,
>>>
>>> I'm running into a little difficulty performing a query that I think
>>> comes up often enough that it should be fairly straightforward.
>>>
>>> I have a schema similar to the following:
>>>
>>> Customer <--- BillingInfo <--- Order
>>>
>>> I've mapped the read-only relationship "orderHistory" from Customer
>> to
>>> Order.
>>>
>>> Now I'd like to perform two queries. One for customers with orders
>>> and
>>> one for customers without. I'd rather not filter in the app itself
>>> because I'd like to paginate the result lists.
>>>
>>> Is there a way to do this without resulting to an SQLTemplate query?
>>>
>>> Thanks,
>>> Kevin
>>>
>>> --
>>> Kevin Menard
>>> Servprise International, Inc.
>>> Remote reboot & power control for your network
>>> www.servprise.com +1 508.892.3823 x308
>>>
>>>
>>>
>
>
RE: Queries based on relationship size
Posted by Kevin Menard <km...@servprise.com>.
Thanks. I'll have to give that a go over the weekend. Unfortunately,
my API structure is centered around SelectQuery and a visitor for adding
expressions and orderings. Changing the root query type will require a
few more changes than I was hoping for. At least it'll give me an
excuse to finally play with EJB QL.
--
Kevin Menard
Servprise International, Inc.
Remote reboot & power control for your network
www.servprise.com +1 508.892.3823 x308
> -----Original Message-----
> From: Andrus Adamchik [mailto:andrus@objectstyle.org]
> Sent: Thursday, February 21, 2008 8:56 AM
> To: user@cayenne.apache.org
> Subject: Re: Queries based on relationship size
>
> EJBQL query with outer joins should help:
>
> 1. select c from Customer c JOIN c.orderHistory
>
> (inner join filters customers with Orders, so this is like a
> regular SelectQuery)
>
> 2. select c from Customer c LEFT JOIN c.orderHistory d WHERE d is null
>
> (outer join combined with the WHERE clause should allow fetching
> customers with no orders)
>
> Two caveats: we still need to add pagination support per CAY-963; I
> may have taken a few _temporary_ shortcuts in the EJBQL parser that
> may cause trouble with flattened relationships, so let me know if you
> run into problems.
>
> Andrus
>
>
>
> On Feb 21, 2008, at 3:05 AM, Kevin Menard wrote:
>
> > Greetings,
> >
> > I'm running into a little difficulty performing a query that I think
> > comes up often enough that it should be fairly straightforward.
> >
> > I have a schema similar to the following:
> >
> > Customer <--- BillingInfo <--- Order
> >
> > I've mapped the read-only relationship "orderHistory" from Customer
> to
> > Order.
> >
> > Now I'd like to perform two queries. One for customers with orders
> > and
> > one for customers without. I'd rather not filter in the app itself
> > because I'd like to paginate the result lists.
> >
> > Is there a way to do this without resulting to an SQLTemplate query?
> >
> > Thanks,
> > Kevin
> >
> > --
> > Kevin Menard
> > Servprise International, Inc.
> > Remote reboot & power control for your network
> > www.servprise.com +1 508.892.3823 x308
> >
> >
> >
Re: Queries based on relationship size
Posted by Andrus Adamchik <an...@objectstyle.org>.
EJBQL query with outer joins should help:
1. select c from Customer c JOIN c.orderHistory
(inner join filters customers with Orders, so this is like a
regular SelectQuery)
2. select c from Customer c LEFT JOIN c.orderHistory d WHERE d is null
(outer join combined with the WHERE clause should allow fetching
customers with no orders)
Two caveats: we still need to add pagination support per CAY-963; I
may have taken a few _temporary_ shortcuts in the EJBQL parser that
may cause trouble with flattened relationships, so let me know if you
run into problems.
Andrus
On Feb 21, 2008, at 3:05 AM, Kevin Menard wrote:
> Greetings,
>
> I'm running into a little difficulty performing a query that I think
> comes up often enough that it should be fairly straightforward.
>
> I have a schema similar to the following:
>
> Customer <--- BillingInfo <--- Order
>
> I've mapped the read-only relationship "orderHistory" from Customer to
> Order.
>
> Now I'd like to perform two queries. One for customers with orders
> and
> one for customers without. I'd rather not filter in the app itself
> because I'd like to paginate the result lists.
>
> Is there a way to do this without resulting to an SQLTemplate query?
>
> Thanks,
> Kevin
>
> --
> Kevin Menard
> Servprise International, Inc.
> Remote reboot & power control for your network
> www.servprise.com +1 508.892.3823 x308
>
>
>