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 <[email protected]>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 <[email protected]> > 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 <[email protected]> > >> > >>> 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 > > > > >
