Thanks Mike et al for the replies.
I think Mike is on the right track as to what I am looking for. I need to
get artists for which there are no paintings as well as artists for which
all paintings have a particular attribute value.
I decided for this case to just use an SQLTemplate with raw SQL and this
seems to be working.
The reason I have not upgraded to 3.0 (for this project) is that I made an
attempt and a lot of things broke (and I am heavily using dataviews which
have been removed from direct inclusion in 3.0).
On Mon, Jan 11, 2010 at 8:03 AM, Mike Kienenberger <mkienen..mail.com>wrote:
> There's a patch somewhere that adds left outer joins to 2.0, probably
> in the jira issue for outer joins. It's used in one of my production
> apps. But it's probably safer to upgrade to 3.0
>
> On Sun, Jan 10, 2010 at 7:30 AM, Andrus Adamchik <andru..bjectstyle.org>
> wrote:
> > If I understand correctly, the goal is to match artists that must have
> > painting that are ALL with the same color. So I think the second OR would
> > match artists that have SOME painting of matching color, and some of a
> > different color.
> >
> > But I guess the important thing here is that Andrey and me showed 2 new
> > capabilities of Cayenne 3.0 that would help with a number of non-trivial
> > queries.
> >
> > Andrus
> >
> > On Jan 10, 2010, at 2:08 PM, Andrey Razumovsky wrote:
> >
> >> Or you can use left joins:
> >> ExpressionFactory.matchExp("paintingsArray+.color",
> >> null).orExp(ExpressionFactory.matchExp("paintingsArray+.color", blue));
> >>
> >> 2010/1/10 Andrus Adamchik <andru..bjectstyle.org>
> >>
> >>> This requires Cayenne 3.0 and EJBQLQuery with subquery. Something like
> >>> this
> >>> might work:
> >>>
> >>> new EJBQLQuery(
> >>> "SELECT a FROM Artist a " +
> >>> "WHERE NOT EXISTS " +
> >>> "(SELECT p FROM Painting p WHERE p.color <> 'XYZ')";
> >>>
> >>> Andrus
> >>>
> >>>
> >>> On Jan 7, 2010, at 2:32 AM, Mark Fraser wrote:
> >>>
> >>> Suppose I have an Artists table and a Paintings table with the usual
> >>> setup
> >>>>
> >>>> of the Artist class having a PAINTINGS_ARRAY property. Suppose
> further
> >>>> that
> >>>> the painting has a "color" property that can be a string or null.
> >>>>
> >>>> How would I build an Expression such that I get back the Artists that
> >>>> have
> >>>> either no paintings or only paintings with a specific color value (or
> a
> >>>> null
> >>>> color value)?
> >>>>
> >>>> I am using Cayenne 2.0.4 in case that matters.
> >>>>
> >>>> TIA
> >>>>
> >>>
> >>>
> >>
> >>
> >> --
> >> Andrey
> >
> >
>
This archive was generated by hypermail 2.0.0 : Tue Jan 12 2010 - 20:38:46 EST