Mailing-List: contact user-help@cayenne.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@cayenne.apache.org
Received-SPF: pass (nike.apache.org: local policy)
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Subject: RE: Queries based on relationship size
Date: Thu, 21 Feb 2008 14:40:44 -0500
Message-ID: 
 <384329B8D7108B45A3064FB38FCF267B19FBFA@aristotle.servprise.office>
In-Reply-To: <E4B67B45-C6F5-4DFB-92FC-D5112D61E1F6@objectstyle.org>
Thread-Topic: Queries based on relationship size
Thread-Index: Ach0kY+8EGspbZrMQx2epK0epZcy3AAL88sA
References: 
 <384329B8D7108B45A3064FB38FCF267B19FBD4@aristotle.servprise.office>
 <E4B67B45-C6F5-4DFB-92FC-D5112D61E1F6@objectstyle.org>
From: "Kevin Menard" <kmenard@servprise.com>
To: <user@cayenne.apache.org>

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.

--=20
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
>=20
> EJBQL query with outer joins should help:
>=20
> 1. select c from Customer c JOIN c.orderHistory
>=20
>     (inner join filters customers with Orders, so this is like a
> regular SelectQuery)
>=20
> 2. select c from Customer c LEFT JOIN c.orderHistory d WHERE d is null
>=20
>     (outer join combined with the WHERE clause should allow fetching
> customers with no orders)
>=20
> 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.
>=20
> Andrus
>=20
>=20
>=20
> On Feb 21, 2008, at 3:05 AM, Kevin Menard wrote:
>=20
> > 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
> >
> >
> >