You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Mark Fraser <ma...@mark100.net> on 2010/01/07 01:32:38 UTC
Expression help
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
Re: Expression help
Posted by Mark Fraser <ma...@mark100.net>.
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 <mk...@gmail.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 <an...@objectstyle.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 <an...@objectstyle.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
> >
> >
>
Re: Expression help
Posted by Mike Kienenberger <mk...@gmail.com>.
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 <an...@objectstyle.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 <an...@objectstyle.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
>
>
Re: Expression help
Posted by Andrus Adamchik <an...@objectstyle.org>.
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 <an...@objectstyle.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
Re: Expression help
Posted by Andrey Razumovsky <ra...@gmail.com>.
Or you can use left joins:
ExpressionFactory.matchExp("paintingsArray+.color",
null).orExp(ExpressionFactory.matchExp("paintingsArray+.color", blue));
2010/1/10 Andrus Adamchik <an...@objectstyle.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
Re: Expression help
Posted by Andrus Adamchik <an...@objectstyle.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