You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Hugi Thordarson <hu...@karlmenn.is> on 2015/07/24 16:51:59 UTC

Expression to look for objects with an empty to-many relationship?

Hi all.
Does Cayenne have an Expression class that allows searching for objects with an empty to-many relationships? If not, any pointers towards a sensible approach?

Cheers,
- hugi

// Hugi Thordarson
// http://www.loftfar.is/ <http://www.loftfar.is/>
// s. 895-6688




Re: Expression to look for objects with an empty to-many relationship?

Posted by Michael Gentry <mg...@masslight.net>.
Andrus/Alex,

If the AggregateUtils I put together is helpful, feel free to use it.

Thanks,

mrg


On Mon, Jul 27, 2015 at 4:49 AM, Andrus Adamchik <an...@objectstyle.org>
wrote:

> And finally we may decide to generate SQL different from OUTER JOIN
> (EXISTS, etc.). Only here this expression becomes truly "new". If you get
> to this step, let's discuss it separately on this list. We will need to
> discuss the behavior and SQL we should generate. Also Alex Kolonitsky is
> now working on a new SelectTranslator to include aggregates, etc., so this
> step will likely overlap with his work.
>

Re: Expression to look for objects with an empty to-many relationship?

Posted by Hugi Thordarson <hu...@karlmenn.is>.
Thanks a lot Andrus! I’m going to play around a little with this and see if I can’t make something useful of it :)

Cheers,
- hugi




> On 27. júl. 2015, at 08:49, Andrus Adamchik <an...@objectstyle.org> wrote:
> 
> In the most general case adding support for a new kind of expression requires the following:
> 
> 1. Support in ExpressionFactory and Property classes (though see comment below on CollectionProperty) 
> 2. Support in Expression parser (so that the expression can be created from String)
> 3. Support in SelectTranslator (so that the expression can be converted into SQL).
> 
> In your case a minimal implementation can be quite simple: implement #1 to work as outer join example ("a.paintingArray+ = null"). So existing #2 and #3 will be reused. Perhaps we create a Property subclass for to-many relationships (CollectionProperty or something) that will have that extra method for empty. Then you will also need to tweak cgen templates so that CollectionProperty is included in generated classes.
> 
> Next step may be adding EJBQL syntax to Expression parser (#2): path IS [NOT] EMPTY
> 
> And finally we may decide to generate SQL different from OUTER JOIN (EXISTS, etc.). Only here this expression becomes truly "new". If you get to this step, let's discuss it separately on this list. We will need to discuss the behavior and SQL we should generate. Also Alex Kolonitsky is now working on a new SelectTranslator to include aggregates, etc., so this step will likely overlap with his work.
> 
> Andrus
> 
> 
>> On Jul 24, 2015, at 7:56 PM, Hugi Thordarson <hu...@karlmenn.is> wrote:
>> 
>> Indeed :). I’ve been looking at the Expression classes for a while now and they look a little daunting. Any recommendations on where to start? Is there a developer guide or an annotated example implementation somewhere?
>> 
>> - hugi
>> 
>> 
>> 
>>> On 24. júl. 2015, at 15:59, Andrus Adamchik <an...@objectstyle.org> wrote:
>>> 
>>> Classic open source - "scratch your own itch" :) 
>>> 
>>> 
>>>> On Jul 24, 2015, at 6:54 PM, Hugi Thordarson <hu...@karlmenn.is> wrote:
>>>> 
>>>> Thanks Andrus, we’ll revert to EJBQL for now.
>>>> 
>>>> Looks like an excellent opportunity to try and implement my first Expression class :)
>>>> 
>>>> - hugi
>>>> 
>>>> 
>>>> 
>>>>> On 24. júl. 2015, at 15:36, Andrus Adamchik <an...@objectstyle.org> wrote:
>>>>> 
>>>>> We should add it. For now OUTER JOIN should do the trick:
>>>>> 
>>>>> "a.paintingArray+ = null"
>>>>> 
>>>>> Also EJBQL syntax supports "IS [NOT] EMPTY" :
>>>>> 
>>>>> "SELECT a FROM Artist a WHERE a.paintingArray IS EMPTY"
>>>>> 
>>>>> Andrus
>>>>> 
>>>>>> On Jul 24, 2015, at 5:51 PM, Hugi Thordarson <hu...@karlmenn.is> wrote:
>>>>>> 
>>>>>> Hi all.
>>>>>> Does Cayenne have an Expression class that allows searching for objects with an empty to-many relationships? If not, any pointers towards a sensible approach?
>>>>>> 
>>>>>> Cheers,
>>>>>> - hugi
>>>>>> 
>>>>>> // Hugi Thordarson
>>>>>> // http://www.loftfar.is/ <http://www.loftfar.is/>
>>>>>> // s. 895-6688
>>>>>> 
>>>>>> 
>>>>>> 
>>>>> 
>>>> 
>>> 
>> 
> 


Re: Expression to look for objects with an empty to-many relationship?

Posted by Andrus Adamchik <an...@objectstyle.org>.
In the most general case adding support for a new kind of expression requires the following:

1. Support in ExpressionFactory and Property classes (though see comment below on CollectionProperty) 
2. Support in Expression parser (so that the expression can be created from String)
3. Support in SelectTranslator (so that the expression can be converted into SQL).

In your case a minimal implementation can be quite simple: implement #1 to work as outer join example ("a.paintingArray+ = null"). So existing #2 and #3 will be reused. Perhaps we create a Property subclass for to-many relationships (CollectionProperty or something) that will have that extra method for empty. Then you will also need to tweak cgen templates so that CollectionProperty is included in generated classes.

Next step may be adding EJBQL syntax to Expression parser (#2): path IS [NOT] EMPTY

And finally we may decide to generate SQL different from OUTER JOIN (EXISTS, etc.). Only here this expression becomes truly "new". If you get to this step, let's discuss it separately on this list. We will need to discuss the behavior and SQL we should generate. Also Alex Kolonitsky is now working on a new SelectTranslator to include aggregates, etc., so this step will likely overlap with his work.

Andrus


> On Jul 24, 2015, at 7:56 PM, Hugi Thordarson <hu...@karlmenn.is> wrote:
> 
> Indeed :). I’ve been looking at the Expression classes for a while now and they look a little daunting. Any recommendations on where to start? Is there a developer guide or an annotated example implementation somewhere?
> 
> - hugi
> 
> 
> 
>> On 24. júl. 2015, at 15:59, Andrus Adamchik <an...@objectstyle.org> wrote:
>> 
>> Classic open source - "scratch your own itch" :) 
>> 
>> 
>>> On Jul 24, 2015, at 6:54 PM, Hugi Thordarson <hu...@karlmenn.is> wrote:
>>> 
>>> Thanks Andrus, we’ll revert to EJBQL for now.
>>> 
>>> Looks like an excellent opportunity to try and implement my first Expression class :)
>>> 
>>> - hugi
>>> 
>>> 
>>> 
>>>> On 24. júl. 2015, at 15:36, Andrus Adamchik <an...@objectstyle.org> wrote:
>>>> 
>>>> We should add it. For now OUTER JOIN should do the trick:
>>>> 
>>>> "a.paintingArray+ = null"
>>>> 
>>>> Also EJBQL syntax supports "IS [NOT] EMPTY" :
>>>> 
>>>> "SELECT a FROM Artist a WHERE a.paintingArray IS EMPTY"
>>>> 
>>>> Andrus
>>>> 
>>>>> On Jul 24, 2015, at 5:51 PM, Hugi Thordarson <hu...@karlmenn.is> wrote:
>>>>> 
>>>>> Hi all.
>>>>> Does Cayenne have an Expression class that allows searching for objects with an empty to-many relationships? If not, any pointers towards a sensible approach?
>>>>> 
>>>>> Cheers,
>>>>> - hugi
>>>>> 
>>>>> // Hugi Thordarson
>>>>> // http://www.loftfar.is/ <http://www.loftfar.is/>
>>>>> // s. 895-6688
>>>>> 
>>>>> 
>>>>> 
>>>> 
>>> 
>> 
> 


Re: Expression to look for objects with an empty to-many relationship?

Posted by Andrus Adamchik <an...@objectstyle.org>.
I'll reply on the dev@ list. 


> On Jul 24, 2015, at 7:56 PM, Hugi Thordarson <hu...@karlmenn.is> wrote:
> 
> Indeed :). I’ve been looking at the Expression classes for a while now and they look a little daunting. Any recommendations on where to start? Is there a developer guide or an annotated example implementation somewhere?
> 
> - hugi
> 
> 
> 
>> On 24. júl. 2015, at 15:59, Andrus Adamchik <an...@objectstyle.org> wrote:
>> 
>> Classic open source - "scratch your own itch" :) 
>> 
>> 
>>> On Jul 24, 2015, at 6:54 PM, Hugi Thordarson <hu...@karlmenn.is> wrote:
>>> 
>>> Thanks Andrus, we’ll revert to EJBQL for now.
>>> 
>>> Looks like an excellent opportunity to try and implement my first Expression class :)
>>> 
>>> - hugi
>>> 
>>> 
>>> 
>>>> On 24. júl. 2015, at 15:36, Andrus Adamchik <an...@objectstyle.org> wrote:
>>>> 
>>>> We should add it. For now OUTER JOIN should do the trick:
>>>> 
>>>> "a.paintingArray+ = null"
>>>> 
>>>> Also EJBQL syntax supports "IS [NOT] EMPTY" :
>>>> 
>>>> "SELECT a FROM Artist a WHERE a.paintingArray IS EMPTY"
>>>> 
>>>> Andrus
>>>> 
>>>>> On Jul 24, 2015, at 5:51 PM, Hugi Thordarson <hu...@karlmenn.is> wrote:
>>>>> 
>>>>> Hi all.
>>>>> Does Cayenne have an Expression class that allows searching for objects with an empty to-many relationships? If not, any pointers towards a sensible approach?
>>>>> 
>>>>> Cheers,
>>>>> - hugi
>>>>> 
>>>>> // Hugi Thordarson
>>>>> // http://www.loftfar.is/ <http://www.loftfar.is/>
>>>>> // s. 895-6688
>>>>> 
>>>>> 
>>>>> 
>>>> 
>>> 
>> 
> 


Re: Expression to look for objects with an empty to-many relationship?

Posted by Hugi Thordarson <hu...@karlmenn.is>.
> It''s been a long time since I've done this (years, Cayenne 1.2 with
> outer join support), but I seem to recall being about to do this with
> a ExpressionFactory.matchExp(path, null).

Well, wouldn’t you know — that actually worked! Although the SQL it generates is a little wonky. It attempts to join on a destination key of null (which of course never exists, so it always returns empty relationships). Haven’t seen it before but perhaps it’s perfectly legal.


> Like Andrus said, you might need to add a "+" in there to force an outer join.
> 
> Sorry I can't be of more help.
> 
> It would be great if you submitted an expression type that supported
> this trivially.

Let’s hope it ends up that way :).

- hugi



> On Fri, Jul 24, 2015 at 12:56 PM, Hugi Thordarson <hu...@karlmenn.is> wrote:
>> Indeed :). I’ve been looking at the Expression classes for a while now and they look a little daunting. Any recommendations on where to start? Is there a developer guide or an annotated example implementation somewhere?
>> 
>> - hugi
>> 
>> 
>> 
>>> On 24. júl. 2015, at 15:59, Andrus Adamchik <an...@objectstyle.org> wrote:
>>> 
>>> Classic open source - "scratch your own itch" :)
>>> 
>>> 
>>>> On Jul 24, 2015, at 6:54 PM, Hugi Thordarson <hu...@karlmenn.is> wrote:
>>>> 
>>>> Thanks Andrus, we’ll revert to EJBQL for now.
>>>> 
>>>> Looks like an excellent opportunity to try and implement my first Expression class :)
>>>> 
>>>> - hugi
>>>> 
>>>> 
>>>> 
>>>>> On 24. júl. 2015, at 15:36, Andrus Adamchik <an...@objectstyle.org> wrote:
>>>>> 
>>>>> We should add it. For now OUTER JOIN should do the trick:
>>>>> 
>>>>> "a.paintingArray+ = null"
>>>>> 
>>>>> Also EJBQL syntax supports "IS [NOT] EMPTY" :
>>>>> 
>>>>> "SELECT a FROM Artist a WHERE a.paintingArray IS EMPTY"
>>>>> 
>>>>> Andrus
>>>>> 
>>>>>> On Jul 24, 2015, at 5:51 PM, Hugi Thordarson <hu...@karlmenn.is> wrote:
>>>>>> 
>>>>>> Hi all.
>>>>>> Does Cayenne have an Expression class that allows searching for objects with an empty to-many relationships? If not, any pointers towards a sensible approach?
>>>>>> 
>>>>>> Cheers,
>>>>>> - hugi
>>>>>> 
>>>>>> // Hugi Thordarson
>>>>>> // http://www.loftfar.is/ <http://www.loftfar.is/>
>>>>>> // s. 895-6688
>>>>>> 
>>>>>> 
>>>>>> 
>>>>> 
>>>> 
>>> 
>> 


Re: Expression to look for objects with an empty to-many relationship?

Posted by Mike Kienenberger <mk...@gmail.com>.
It''s been a long time since I've done this (years, Cayenne 1.2 with
outer join support), but I seem to recall being about to do this with
a ExpressionFactory.matchExp(path, null).

Like Andrus said, you might need to add a "+" in there to force an outer join.

Sorry I can't be of more help.

It would be great if you submitted an expression type that supported
this trivially.



On Fri, Jul 24, 2015 at 12:56 PM, Hugi Thordarson <hu...@karlmenn.is> wrote:
> Indeed :). I’ve been looking at the Expression classes for a while now and they look a little daunting. Any recommendations on where to start? Is there a developer guide or an annotated example implementation somewhere?
>
> - hugi
>
>
>
>> On 24. júl. 2015, at 15:59, Andrus Adamchik <an...@objectstyle.org> wrote:
>>
>> Classic open source - "scratch your own itch" :)
>>
>>
>>> On Jul 24, 2015, at 6:54 PM, Hugi Thordarson <hu...@karlmenn.is> wrote:
>>>
>>> Thanks Andrus, we’ll revert to EJBQL for now.
>>>
>>> Looks like an excellent opportunity to try and implement my first Expression class :)
>>>
>>> - hugi
>>>
>>>
>>>
>>>> On 24. júl. 2015, at 15:36, Andrus Adamchik <an...@objectstyle.org> wrote:
>>>>
>>>> We should add it. For now OUTER JOIN should do the trick:
>>>>
>>>> "a.paintingArray+ = null"
>>>>
>>>> Also EJBQL syntax supports "IS [NOT] EMPTY" :
>>>>
>>>> "SELECT a FROM Artist a WHERE a.paintingArray IS EMPTY"
>>>>
>>>> Andrus
>>>>
>>>>> On Jul 24, 2015, at 5:51 PM, Hugi Thordarson <hu...@karlmenn.is> wrote:
>>>>>
>>>>> Hi all.
>>>>> Does Cayenne have an Expression class that allows searching for objects with an empty to-many relationships? If not, any pointers towards a sensible approach?
>>>>>
>>>>> Cheers,
>>>>> - hugi
>>>>>
>>>>> // Hugi Thordarson
>>>>> // http://www.loftfar.is/ <http://www.loftfar.is/>
>>>>> // s. 895-6688
>>>>>
>>>>>
>>>>>
>>>>
>>>
>>
>

Re: Expression to look for objects with an empty to-many relationship?

Posted by Ricardo Parada <rp...@mac.com>.
On the EOF/Wonder world we have ERXExistsQualifier. I don't know how different cayenne is but it might be worth looking at it and try to mimic it. 

For example, if you have a Family entity. a Pet entity and a pets to-many relationship from family to Pet then you would be able to build qualifiers than answer questions like these:

Get families with pets
Get families with zero pets
Get families with puppies (where puppies is a sub qualifier on the Pet entity)
Etc...

The qualifiers work in memory or database equally well. 

> On Jul 24, 2015, at 12:56 PM, Hugi Thordarson <hu...@karlmenn.is> wrote:
> 
> Indeed :). I’ve been looking at the Expression classes for a while now and they look a little daunting. Any recommendations on where to start? Is there a developer guide or an annotated example implementation somewhere?
> 
> - hugi
> 
> 
> 
>> On 24. júl. 2015, at 15:59, Andrus Adamchik <an...@objectstyle.org> wrote:
>> 
>> Classic open source - "scratch your own itch" :) 
>> 
>> 
>>> On Jul 24, 2015, at 6:54 PM, Hugi Thordarson <hu...@karlmenn.is> wrote:
>>> 
>>> Thanks Andrus, we’ll revert to EJBQL for now.
>>> 
>>> Looks like an excellent opportunity to try and implement my first Expression class :)
>>> 
>>> - hugi
>>> 
>>> 
>>> 
>>>> On 24. júl. 2015, at 15:36, Andrus Adamchik <an...@objectstyle.org> wrote:
>>>> 
>>>> We should add it. For now OUTER JOIN should do the trick:
>>>> 
>>>> "a.paintingArray+ = null"
>>>> 
>>>> Also EJBQL syntax supports "IS [NOT] EMPTY" :
>>>> 
>>>> "SELECT a FROM Artist a WHERE a.paintingArray IS EMPTY"
>>>> 
>>>> Andrus
>>>> 
>>>>> On Jul 24, 2015, at 5:51 PM, Hugi Thordarson <hu...@karlmenn.is> wrote:
>>>>> 
>>>>> Hi all.
>>>>> Does Cayenne have an Expression class that allows searching for objects with an empty to-many relationships? If not, any pointers towards a sensible approach?
>>>>> 
>>>>> Cheers,
>>>>> - hugi
>>>>> 
>>>>> // Hugi Thordarson
>>>>> // http://www.loftfar.is/ <http://www.loftfar.is/>
>>>>> // s. 895-6688
> 

Re: Expression to look for objects with an empty to-many relationship?

Posted by Hugi Thordarson <hu...@karlmenn.is>.
Indeed :). I’ve been looking at the Expression classes for a while now and they look a little daunting. Any recommendations on where to start? Is there a developer guide or an annotated example implementation somewhere?

- hugi



> On 24. júl. 2015, at 15:59, Andrus Adamchik <an...@objectstyle.org> wrote:
> 
> Classic open source - "scratch your own itch" :) 
> 
> 
>> On Jul 24, 2015, at 6:54 PM, Hugi Thordarson <hu...@karlmenn.is> wrote:
>> 
>> Thanks Andrus, we’ll revert to EJBQL for now.
>> 
>> Looks like an excellent opportunity to try and implement my first Expression class :)
>> 
>> - hugi
>> 
>> 
>> 
>>> On 24. júl. 2015, at 15:36, Andrus Adamchik <an...@objectstyle.org> wrote:
>>> 
>>> We should add it. For now OUTER JOIN should do the trick:
>>> 
>>> "a.paintingArray+ = null"
>>> 
>>> Also EJBQL syntax supports "IS [NOT] EMPTY" :
>>> 
>>> "SELECT a FROM Artist a WHERE a.paintingArray IS EMPTY"
>>> 
>>> Andrus
>>> 
>>>> On Jul 24, 2015, at 5:51 PM, Hugi Thordarson <hu...@karlmenn.is> wrote:
>>>> 
>>>> Hi all.
>>>> Does Cayenne have an Expression class that allows searching for objects with an empty to-many relationships? If not, any pointers towards a sensible approach?
>>>> 
>>>> Cheers,
>>>> - hugi
>>>> 
>>>> // Hugi Thordarson
>>>> // http://www.loftfar.is/ <http://www.loftfar.is/>
>>>> // s. 895-6688
>>>> 
>>>> 
>>>> 
>>> 
>> 
> 


Re: Expression to look for objects with an empty to-many relationship?

Posted by Andrus Adamchik <an...@objectstyle.org>.
Classic open source - "scratch your own itch" :) 


> On Jul 24, 2015, at 6:54 PM, Hugi Thordarson <hu...@karlmenn.is> wrote:
> 
> Thanks Andrus, we’ll revert to EJBQL for now.
> 
> Looks like an excellent opportunity to try and implement my first Expression class :)
> 
> - hugi
> 
> 
> 
>> On 24. júl. 2015, at 15:36, Andrus Adamchik <an...@objectstyle.org> wrote:
>> 
>> We should add it. For now OUTER JOIN should do the trick:
>> 
>>  "a.paintingArray+ = null"
>> 
>> Also EJBQL syntax supports "IS [NOT] EMPTY" :
>> 
>>  "SELECT a FROM Artist a WHERE a.paintingArray IS EMPTY"
>> 
>> Andrus
>> 
>>> On Jul 24, 2015, at 5:51 PM, Hugi Thordarson <hu...@karlmenn.is> wrote:
>>> 
>>> Hi all.
>>> Does Cayenne have an Expression class that allows searching for objects with an empty to-many relationships? If not, any pointers towards a sensible approach?
>>> 
>>> Cheers,
>>> - hugi
>>> 
>>> // Hugi Thordarson
>>> // http://www.loftfar.is/ <http://www.loftfar.is/>
>>> // s. 895-6688
>>> 
>>> 
>>> 
>> 
> 


Re: Expression to look for objects with an empty to-many relationship?

Posted by Hugi Thordarson <hu...@karlmenn.is>.
Thanks Andrus, we’ll revert to EJBQL for now.

Looks like an excellent opportunity to try and implement my first Expression class :)

- hugi



> On 24. júl. 2015, at 15:36, Andrus Adamchik <an...@objectstyle.org> wrote:
> 
> We should add it. For now OUTER JOIN should do the trick:
> 
>   "a.paintingArray+ = null"
> 
> Also EJBQL syntax supports "IS [NOT] EMPTY" :
> 
>   "SELECT a FROM Artist a WHERE a.paintingArray IS EMPTY"
> 
> Andrus
> 
>> On Jul 24, 2015, at 5:51 PM, Hugi Thordarson <hu...@karlmenn.is> wrote:
>> 
>> Hi all.
>> Does Cayenne have an Expression class that allows searching for objects with an empty to-many relationships? If not, any pointers towards a sensible approach?
>> 
>> Cheers,
>> - hugi
>> 
>> // Hugi Thordarson
>> // http://www.loftfar.is/ <http://www.loftfar.is/>
>> // s. 895-6688
>> 
>> 
>> 
> 


Re: Expression to look for objects with an empty to-many relationship?

Posted by Andrus Adamchik <an...@objectstyle.org>.
We should add it. For now OUTER JOIN should do the trick:

   "a.paintingArray+ = null"

Also EJBQL syntax supports "IS [NOT] EMPTY" :

   "SELECT a FROM Artist a WHERE a.paintingArray IS EMPTY"

Andrus

> On Jul 24, 2015, at 5:51 PM, Hugi Thordarson <hu...@karlmenn.is> wrote:
> 
> Hi all.
> Does Cayenne have an Expression class that allows searching for objects with an empty to-many relationships? If not, any pointers towards a sensible approach?
> 
> Cheers,
> - hugi
> 
> // Hugi Thordarson
> // http://www.loftfar.is/ <http://www.loftfar.is/>
> // s. 895-6688
> 
> 
>