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/09/22 16:36:49 UTC
Fetching distinct related objects
Hi all,
I’m trying to retrieve a list of distinct related objects. If I execute an EJBQL-query that looks like this…
"select distinct receipt.shop from Receipt receipt”
…where “shop” is a relationship on the “Receipt”-entity, I get an exception that looks like this:
https://gist.github.com/hugith/c3f48c4576e6a55383b3
Doing the same fetch without the distinct keyword works fine (returns a list of Shop objects). Also, performing a distinct fetch on attributes of “Shop” works fine, like so:
"select distinct receipt.shop.name from Receipt receipt”
Is this a bug or am I going around this the wrong way? How would you go about constructing a fetch for distinct related objects?
Cheers,
- hugi
// Hugi Thordarson
// http://www.loftfar.is/
// s. 895-6688
Re: Fetching distinct related objects
Posted by Hugi Thordarson <hu...@karlmenn.is>.
> I'd say you should and you are very much encouraged to do it :)
:)
> In fact this one probably goes way back, when we still tried to work within the JPA spec constraints. So this may have been an intentional limitation that we should no longer abide by. Try this workaround - replacing the relationship in SELECT clause with an alias, and let us know if that made a difference:
>
> "select distinct s from Receipt receipt inner join receipt.shop s”
Yes, this syntax works, thanks.
Cheers,
- hugi
> Andrus
>
>
>> On Sep 23, 2015, at 10:40 AM, Hugi Thordarson <hu...@karlmenn.is> wrote:
>>
>> What? Are you saying I shouldn’t be using pre-release software in a production environment? Ghastly! ;)
>>
>> Filed:
>> https://issues.apache.org/jira/browse/CAY-2031
>>
>> Thanks,
>> - hugi
>>
>>
>>
>>> On 23. sep. 2015, at 07:13, Andrus Adamchik <an...@objectstyle.org> wrote:
>>>
>>> This is a bug.
>>>
>>>> On Sep 22, 2015, at 5:36 PM, Hugi Thordarson <hu...@karlmenn.is> wrote:
>>>>
>>>> Hi all,
>>>>
>>>> I’m trying to retrieve a list of distinct related objects. If I execute an EJBQL-query that looks like this…
>>>>
>>>> "select distinct receipt.shop from Receipt receipt”
>>>>
>>>> …where “shop” is a relationship on the “Receipt”-entity, I get an exception that looks like this:
>>>>
>>>> https://gist.github.com/hugith/c3f48c4576e6a55383b3
>>>>
>>>> Doing the same fetch without the distinct keyword works fine (returns a list of Shop objects). Also, performing a distinct fetch on attributes of “Shop” works fine, like so:
>>>>
>>>> "select distinct receipt.shop.name from Receipt receipt”
>>>>
>>>> Is this a bug or am I going around this the wrong way? How would you go about constructing a fetch for distinct related objects?
>>>>
>>>> Cheers,
>>>> - hugi
>>>>
>>>> // Hugi Thordarson
>>>> // http://www.loftfar.is/
>>>> // s. 895-6688
>>>
>>
>
Re: Fetching distinct related objects
Posted by Andrus Adamchik <an...@objectstyle.org>.
I'd say you should and you are very much encouraged to do it :)
In fact this one probably goes way back, when we still tried to work within the JPA spec constraints. So this may have been an intentional limitation that we should no longer abide by. Try this workaround - replacing the relationship in SELECT clause with an alias, and let us know if that made a difference:
"select distinct s from Receipt receipt inner join receipt.shop s”
Andrus
> On Sep 23, 2015, at 10:40 AM, Hugi Thordarson <hu...@karlmenn.is> wrote:
>
> What? Are you saying I shouldn’t be using pre-release software in a production environment? Ghastly! ;)
>
> Filed:
> https://issues.apache.org/jira/browse/CAY-2031
>
> Thanks,
> - hugi
>
>
>
>> On 23. sep. 2015, at 07:13, Andrus Adamchik <an...@objectstyle.org> wrote:
>>
>> This is a bug.
>>
>>> On Sep 22, 2015, at 5:36 PM, Hugi Thordarson <hu...@karlmenn.is> wrote:
>>>
>>> Hi all,
>>>
>>> I’m trying to retrieve a list of distinct related objects. If I execute an EJBQL-query that looks like this…
>>>
>>> "select distinct receipt.shop from Receipt receipt”
>>>
>>> …where “shop” is a relationship on the “Receipt”-entity, I get an exception that looks like this:
>>>
>>> https://gist.github.com/hugith/c3f48c4576e6a55383b3
>>>
>>> Doing the same fetch without the distinct keyword works fine (returns a list of Shop objects). Also, performing a distinct fetch on attributes of “Shop” works fine, like so:
>>>
>>> "select distinct receipt.shop.name from Receipt receipt”
>>>
>>> Is this a bug or am I going around this the wrong way? How would you go about constructing a fetch for distinct related objects?
>>>
>>> Cheers,
>>> - hugi
>>>
>>> // Hugi Thordarson
>>> // http://www.loftfar.is/
>>> // s. 895-6688
>>
>
Re: Fetching distinct related objects
Posted by Hugi Thordarson <hu...@karlmenn.is>.
What? Are you saying I shouldn’t be using pre-release software in a production environment? Ghastly! ;)
Filed:
https://issues.apache.org/jira/browse/CAY-2031
Thanks,
- hugi
> On 23. sep. 2015, at 07:13, Andrus Adamchik <an...@objectstyle.org> wrote:
>
> This is a bug.
>
>> On Sep 22, 2015, at 5:36 PM, Hugi Thordarson <hu...@karlmenn.is> wrote:
>>
>> Hi all,
>>
>> I’m trying to retrieve a list of distinct related objects. If I execute an EJBQL-query that looks like this…
>>
>> "select distinct receipt.shop from Receipt receipt”
>>
>> …where “shop” is a relationship on the “Receipt”-entity, I get an exception that looks like this:
>>
>> https://gist.github.com/hugith/c3f48c4576e6a55383b3
>>
>> Doing the same fetch without the distinct keyword works fine (returns a list of Shop objects). Also, performing a distinct fetch on attributes of “Shop” works fine, like so:
>>
>> "select distinct receipt.shop.name from Receipt receipt”
>>
>> Is this a bug or am I going around this the wrong way? How would you go about constructing a fetch for distinct related objects?
>>
>> Cheers,
>> - hugi
>>
>> // Hugi Thordarson
>> // http://www.loftfar.is/
>> // s. 895-6688
>
Re: Fetching distinct related objects
Posted by Andrus Adamchik <an...@objectstyle.org>.
This is a bug.
> On Sep 22, 2015, at 5:36 PM, Hugi Thordarson <hu...@karlmenn.is> wrote:
>
> Hi all,
>
> I’m trying to retrieve a list of distinct related objects. If I execute an EJBQL-query that looks like this…
>
> "select distinct receipt.shop from Receipt receipt”
>
> …where “shop” is a relationship on the “Receipt”-entity, I get an exception that looks like this:
>
> https://gist.github.com/hugith/c3f48c4576e6a55383b3
>
> Doing the same fetch without the distinct keyword works fine (returns a list of Shop objects). Also, performing a distinct fetch on attributes of “Shop” works fine, like so:
>
> "select distinct receipt.shop.name from Receipt receipt”
>
> Is this a bug or am I going around this the wrong way? How would you go about constructing a fetch for distinct related objects?
>
> Cheers,
> - hugi
>
> // Hugi Thordarson
> // http://www.loftfar.is/
> // s. 895-6688
Re: Fetching distinct related objects
Posted by Hugi Thordarson <hu...@karlmenn.is>.
Yes, true—I just submitted the simplest possible EJBQL example that fails. I’m actually trying to implement this functionality in a utility method, as seen here (the second method in the class, distinctValues).
https://bitbucket.org/hugi/uswebobjects/src/1b5389335513134f2d498a8576e965197e32124c/Sources/is/rebbi/wo/cayenne/CayenneUtils.java?at=master&fileviewer=file-view-default
As I mentioned, this method works fine when the properties I’m fetching are regular attributes, but fail when I’m attempting to fetch objects (i.e. “shop” rather than “shop.name”).
Cheers,
- hugi
> On 22. sep. 2015, at 15:55, John Huss <jo...@gmail.com> wrote:
>
> Sorry, I misunderstood. I assume you're actually including a qualifier or
> where clause, not just fetching everything?
>
> On Tue, Sep 22, 2015 at 10:39 AM Hugi Thordarson <hu...@karlmenn.is> wrote:
>
>> Doesn't setDistinct always work with the entire row? I’m attempting to
>> fetch just the distinct values of certain relationships. I haven’t found an
>> API for that in Cayenne, that’s why I’m using EJBQL.
>>
>> Then again, I might be on the wrong track…
>>
>> Thanks,
>> - hugi
>>
>>
>>
>>> On 22. sep. 2015, at 15:29, John Huss <jo...@gmail.com> wrote:
>>>
>>> selectQuery.setDistinct(true);
>>>
>>> On Tue, Sep 22, 2015 at 9:37 AM Hugi Thordarson <hu...@karlmenn.is>
>> wrote:
>>>
>>>> Hi all,
>>>>
>>>> I’m trying to retrieve a list of distinct related objects. If I execute
>> an
>>>> EJBQL-query that looks like this…
>>>>
>>>> "select distinct receipt.shop from Receipt receipt”
>>>>
>>>> …where “shop” is a relationship on the “Receipt”-entity, I get an
>>>> exception that looks like this:
>>>>
>>>> https://gist.github.com/hugith/c3f48c4576e6a55383b3
>>>>
>>>> Doing the same fetch without the distinct keyword works fine (returns a
>>>> list of Shop objects). Also, performing a distinct fetch on attributes
>> of
>>>> “Shop” works fine, like so:
>>>>
>>>> "select distinct receipt.shop.name from Receipt receipt”
>>>>
>>>> Is this a bug or am I going around this the wrong way? How would you go
>>>> about constructing a fetch for distinct related objects?
>>>>
>>>> Cheers,
>>>> - hugi
>>>>
>>>> // Hugi Thordarson
>>>> // http://www.loftfar.is/
>>>> // s. 895-6688
>>
>>
Re: Fetching distinct related objects
Posted by John Huss <jo...@gmail.com>.
Sorry, I misunderstood. I assume you're actually including a qualifier or
where clause, not just fetching everything?
On Tue, Sep 22, 2015 at 10:39 AM Hugi Thordarson <hu...@karlmenn.is> wrote:
> Doesn't setDistinct always work with the entire row? I’m attempting to
> fetch just the distinct values of certain relationships. I haven’t found an
> API for that in Cayenne, that’s why I’m using EJBQL.
>
> Then again, I might be on the wrong track…
>
> Thanks,
> - hugi
>
>
>
> > On 22. sep. 2015, at 15:29, John Huss <jo...@gmail.com> wrote:
> >
> > selectQuery.setDistinct(true);
> >
> > On Tue, Sep 22, 2015 at 9:37 AM Hugi Thordarson <hu...@karlmenn.is>
> wrote:
> >
> >> Hi all,
> >>
> >> I’m trying to retrieve a list of distinct related objects. If I execute
> an
> >> EJBQL-query that looks like this…
> >>
> >> "select distinct receipt.shop from Receipt receipt”
> >>
> >> …where “shop” is a relationship on the “Receipt”-entity, I get an
> >> exception that looks like this:
> >>
> >> https://gist.github.com/hugith/c3f48c4576e6a55383b3
> >>
> >> Doing the same fetch without the distinct keyword works fine (returns a
> >> list of Shop objects). Also, performing a distinct fetch on attributes
> of
> >> “Shop” works fine, like so:
> >>
> >> "select distinct receipt.shop.name from Receipt receipt”
> >>
> >> Is this a bug or am I going around this the wrong way? How would you go
> >> about constructing a fetch for distinct related objects?
> >>
> >> Cheers,
> >> - hugi
> >>
> >> // Hugi Thordarson
> >> // http://www.loftfar.is/
> >> // s. 895-6688
>
>
Re: Fetching distinct related objects
Posted by Hugi Thordarson <hu...@karlmenn.is>.
Doesn't setDistinct always work with the entire row? I’m attempting to fetch just the distinct values of certain relationships. I haven’t found an API for that in Cayenne, that’s why I’m using EJBQL.
Then again, I might be on the wrong track…
Thanks,
- hugi
> On 22. sep. 2015, at 15:29, John Huss <jo...@gmail.com> wrote:
>
> selectQuery.setDistinct(true);
>
> On Tue, Sep 22, 2015 at 9:37 AM Hugi Thordarson <hu...@karlmenn.is> wrote:
>
>> Hi all,
>>
>> I’m trying to retrieve a list of distinct related objects. If I execute an
>> EJBQL-query that looks like this…
>>
>> "select distinct receipt.shop from Receipt receipt”
>>
>> …where “shop” is a relationship on the “Receipt”-entity, I get an
>> exception that looks like this:
>>
>> https://gist.github.com/hugith/c3f48c4576e6a55383b3
>>
>> Doing the same fetch without the distinct keyword works fine (returns a
>> list of Shop objects). Also, performing a distinct fetch on attributes of
>> “Shop” works fine, like so:
>>
>> "select distinct receipt.shop.name from Receipt receipt”
>>
>> Is this a bug or am I going around this the wrong way? How would you go
>> about constructing a fetch for distinct related objects?
>>
>> Cheers,
>> - hugi
>>
>> // Hugi Thordarson
>> // http://www.loftfar.is/
>> // s. 895-6688
Re: Fetching distinct related objects
Posted by John Huss <jo...@gmail.com>.
selectQuery.setDistinct(true);
On Tue, Sep 22, 2015 at 9:37 AM Hugi Thordarson <hu...@karlmenn.is> wrote:
> Hi all,
>
> I’m trying to retrieve a list of distinct related objects. If I execute an
> EJBQL-query that looks like this…
>
> "select distinct receipt.shop from Receipt receipt”
>
> …where “shop” is a relationship on the “Receipt”-entity, I get an
> exception that looks like this:
>
> https://gist.github.com/hugith/c3f48c4576e6a55383b3
>
> Doing the same fetch without the distinct keyword works fine (returns a
> list of Shop objects). Also, performing a distinct fetch on attributes of
> “Shop” works fine, like so:
>
> "select distinct receipt.shop.name from Receipt receipt”
>
> Is this a bug or am I going around this the wrong way? How would you go
> about constructing a fetch for distinct related objects?
>
> Cheers,
> - hugi
>
> // Hugi Thordarson
> // http://www.loftfar.is/
> // s. 895-6688