You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by Daryl Stultz <da...@6degrees.com> on 2010/02/03 21:00:23 UTC
Re: JPQL to get association members, but only ones that fulfill a
condition
On Wed, Feb 3, 2010 at 2:12 PM, KARR, DAVID (ATTCINW) <dk...@att.com>wrote:
> So I changed my query to:
>
> select foo from packagepath.Foo foo left join fetch foo.childBars as bar
> where foo.id=:id and current_date between bar.startDate and
> bar.endDate
>
> try this:
select distinct foo from packagepath.Foo foo
left join foo.childBars as bar
left join fetch foo.childBars
where foo.id=:id
and current_date between bar.startDate and bar.endDate
Notice "distinct". You might find it works without out it bug a bug will
bite you later...
I'm not sure if you are expecting to get a subset of foo.childBars. If you
are, this won't work.
--
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
mailto:daryl@6degrees.com
Re: JPQL to get association members,
but only ones that fulfill a condition
Posted by Craig L Russell <Cr...@Sun.COM>.
Hi,
On Feb 3, 2010, at 1:27 PM, KARR, DAVID (ATTCINW) wrote:
>> -----Original Message-----
>> From: Craig.Russell@Sun.COM [mailto:Craig.Russell@Sun.COM]
>> Sent: Wednesday, February 03, 2010 12:31 PM
>> To: users@openjpa.apache.org
>> Subject: Re: JPQL to get association members, but only ones that
>> fulfill a condition
>>
>> Perhaps you could try using the bar as the root of the query by
>> selecting the bar and joining the foo parent. If the foo id is a
>> simple foreign key in bar this might be the most efficient way to get
>> the data anyway.
>
> The association is through a join table. This is making my brain
> hurt.
> :)
You can still use the suggested technique. You have defined the
association such that JPA can navigate the relationship from either
side.
Craig
>
> For now I'm going to just implement manual filtering outside of the
> transaction, but I suppose I could build a native query for the
> filtered
> list and ignore the mapping for the one-to-many and just use the
> native
> query to build that filtered list.
>
> I see that Hibernate has a "formula" attribute on a mapped field which
> can specify a native SQL expression. I guess that would have been
> useful here.
>
>> On Feb 3, 2010, at 12:23 PM, KARR, DAVID (ATTCINW) wrote:
>>
>>>> -----Original Message-----
>>>> From: Daryl Stultz [mailto:daryl@6degrees.com]
>>>> Sent: Wednesday, February 03, 2010 12:00 PM
>>>> To: users@openjpa.apache.org
>>>> Subject: Re: JPQL to get association members, but only ones that
>>>> fulfill a condition
>>>>
>>>> On Wed, Feb 3, 2010 at 2:12 PM, KARR, DAVID (ATTCINW)
>>>> <dk...@att.com>wrote:
>>>>
>>>>> So I changed my query to:
>>>>>
>>>>> select foo from packagepath.Foo foo left join fetch foo.childBars
>> as
>>>> bar
>>>>> where foo.id=:id and current_date between bar.startDate and
>>>>> bar.endDate
>>>>>
>>>>> try this:
>>>>
>>>> select distinct foo from packagepath.Foo foo
>>>> left join foo.childBars as bar
>>>> left join fetch foo.childBars
>>>> where foo.id=:id
>>>> and current_date between bar.startDate and bar.endDate
>>>>
>>>> Notice "distinct". You might find it works without out it bug a bug
>>>> will
>>>> bite you later...
>>>>
>>>> I'm not sure if you are expecting to get a subset of foo.childBars.
>>>> If
>>>> you
>>>> are, this won't work.
>>>
>>> I don't understand the last statement here.
>>>
>>> When I tried this strategy, it resulted in no rows returned, and I
>>> know
>>> that there's at least one "bar" with a current date range, but I
> know
>>> there are several that do not. I tried both with and without
>>> "distinct", with the same result.
>>>
>>> I have a feeling I'm heading towards having to construct a specific
>>> query for the bars that are child of this foo and are in the date
>>> range.
>>
>> Craig L Russell
>> Architect, Sun Java Enterprise System http://db.apache.org/jdo
>> 408 276-5638 mailto:Craig.Russell@sun.com
>> P.S. A good JDO? O, Gasp!
>
Craig L Russell
Architect, Sun Java Enterprise System http://db.apache.org/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!
RE: JPQL to get association members, but only ones that fulfill a condition
Posted by "KARR, DAVID (ATTCINW)" <dk...@att.com>.
> -----Original Message-----
> From: Craig.Russell@Sun.COM [mailto:Craig.Russell@Sun.COM]
> Sent: Wednesday, February 03, 2010 12:31 PM
> To: users@openjpa.apache.org
> Subject: Re: JPQL to get association members, but only ones that
> fulfill a condition
>
> Perhaps you could try using the bar as the root of the query by
> selecting the bar and joining the foo parent. If the foo id is a
> simple foreign key in bar this might be the most efficient way to get
> the data anyway.
The association is through a join table. This is making my brain hurt.
:)
For now I'm going to just implement manual filtering outside of the
transaction, but I suppose I could build a native query for the filtered
list and ignore the mapping for the one-to-many and just use the native
query to build that filtered list.
I see that Hibernate has a "formula" attribute on a mapped field which
can specify a native SQL expression. I guess that would have been
useful here.
> On Feb 3, 2010, at 12:23 PM, KARR, DAVID (ATTCINW) wrote:
>
> >> -----Original Message-----
> >> From: Daryl Stultz [mailto:daryl@6degrees.com]
> >> Sent: Wednesday, February 03, 2010 12:00 PM
> >> To: users@openjpa.apache.org
> >> Subject: Re: JPQL to get association members, but only ones that
> >> fulfill a condition
> >>
> >> On Wed, Feb 3, 2010 at 2:12 PM, KARR, DAVID (ATTCINW)
> >> <dk...@att.com>wrote:
> >>
> >>> So I changed my query to:
> >>>
> >>> select foo from packagepath.Foo foo left join fetch foo.childBars
> as
> >> bar
> >>> where foo.id=:id and current_date between bar.startDate and
> >>> bar.endDate
> >>>
> >>> try this:
> >>
> >> select distinct foo from packagepath.Foo foo
> >> left join foo.childBars as bar
> >> left join fetch foo.childBars
> >> where foo.id=:id
> >> and current_date between bar.startDate and bar.endDate
> >>
> >> Notice "distinct". You might find it works without out it bug a bug
> >> will
> >> bite you later...
> >>
> >> I'm not sure if you are expecting to get a subset of foo.childBars.
> >> If
> >> you
> >> are, this won't work.
> >
> > I don't understand the last statement here.
> >
> > When I tried this strategy, it resulted in no rows returned, and I
> > know
> > that there's at least one "bar" with a current date range, but I
know
> > there are several that do not. I tried both with and without
> > "distinct", with the same result.
> >
> > I have a feeling I'm heading towards having to construct a specific
> > query for the bars that are child of this foo and are in the date
> > range.
>
> Craig L Russell
> Architect, Sun Java Enterprise System http://db.apache.org/jdo
> 408 276-5638 mailto:Craig.Russell@sun.com
> P.S. A good JDO? O, Gasp!
Re: JPQL to get association members,
but only ones that fulfill a condition
Posted by Craig L Russell <Cr...@Sun.COM>.
Perhaps you could try using the bar as the root of the query by
selecting the bar and joining the foo parent. If the foo id is a
simple foreign key in bar this might be the most efficient way to get
the data anyway.
Craig
On Feb 3, 2010, at 12:23 PM, KARR, DAVID (ATTCINW) wrote:
>> -----Original Message-----
>> From: Daryl Stultz [mailto:daryl@6degrees.com]
>> Sent: Wednesday, February 03, 2010 12:00 PM
>> To: users@openjpa.apache.org
>> Subject: Re: JPQL to get association members, but only ones that
>> fulfill a condition
>>
>> On Wed, Feb 3, 2010 at 2:12 PM, KARR, DAVID (ATTCINW)
>> <dk...@att.com>wrote:
>>
>>> So I changed my query to:
>>>
>>> select foo from packagepath.Foo foo left join fetch foo.childBars as
>> bar
>>> where foo.id=:id and current_date between bar.startDate and
>>> bar.endDate
>>>
>>> try this:
>>
>> select distinct foo from packagepath.Foo foo
>> left join foo.childBars as bar
>> left join fetch foo.childBars
>> where foo.id=:id
>> and current_date between bar.startDate and bar.endDate
>>
>> Notice "distinct". You might find it works without out it bug a bug
>> will
>> bite you later...
>>
>> I'm not sure if you are expecting to get a subset of foo.childBars.
>> If
>> you
>> are, this won't work.
>
> I don't understand the last statement here.
>
> When I tried this strategy, it resulted in no rows returned, and I
> know
> that there's at least one "bar" with a current date range, but I know
> there are several that do not. I tried both with and without
> "distinct", with the same result.
>
> I have a feeling I'm heading towards having to construct a specific
> query for the bars that are child of this foo and are in the date
> range.
Craig L Russell
Architect, Sun Java Enterprise System http://db.apache.org/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!
Re: JPQL to get association members, but only ones that fulfill a
condition
Posted by Daryl Stultz <da...@6degrees.com>.
On Wed, Feb 3, 2010 at 3:23 PM, KARR, DAVID (ATTCINW) <dk...@att.com>wrote:
> > I'm not sure if you are expecting to get a subset of foo.childBars. If
> > you
> > are, this won't work.
>
> I don't understand the last statement here.
>
>
If the foo you are interested in has 5 bar children, but only 3 match the
date range, you might expect to get the foo plus the 3 matching children.
When I first started out, that's what I thought I'd get, but you'll get all
5 with the fetch join. (The join without distinct will get you 3 rows of the
same foo, but each foo will have all 5 in childBars.) You need to reverse
things as Craig suggested to get that. Sorry if I suggested you are as
"naive" as I was when I started! :-)
--
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
mailto:daryl@6degrees.com
RE: JPQL to get association members, but only ones that fulfill a condition
Posted by "KARR, DAVID (ATTCINW)" <dk...@att.com>.
> -----Original Message-----
> From: Daryl Stultz [mailto:daryl@6degrees.com]
> Sent: Wednesday, February 03, 2010 12:00 PM
> To: users@openjpa.apache.org
> Subject: Re: JPQL to get association members, but only ones that
> fulfill a condition
>
> On Wed, Feb 3, 2010 at 2:12 PM, KARR, DAVID (ATTCINW)
> <dk...@att.com>wrote:
>
> > So I changed my query to:
> >
> > select foo from packagepath.Foo foo left join fetch foo.childBars as
> bar
> > where foo.id=:id and current_date between bar.startDate and
> > bar.endDate
> >
> > try this:
>
> select distinct foo from packagepath.Foo foo
> left join foo.childBars as bar
> left join fetch foo.childBars
> where foo.id=:id
> and current_date between bar.startDate and bar.endDate
>
> Notice "distinct". You might find it works without out it bug a bug
> will
> bite you later...
>
> I'm not sure if you are expecting to get a subset of foo.childBars. If
> you
> are, this won't work.
I don't understand the last statement here.
When I tried this strategy, it resulted in no rows returned, and I know
that there's at least one "bar" with a current date range, but I know
there are several that do not. I tried both with and without
"distinct", with the same result.
I have a feeling I'm heading towards having to construct a specific
query for the bars that are child of this foo and are in the date range.