Hi,
On Feb 3, 2010, at 1:27 PM, KARR, DAVID (ATTCINW) wrote:
-----Original Message-----
From: [email protected] [mailto:[email protected]]
Sent: Wednesday, February 03, 2010 12:31 PM
To: [email protected]
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:[email protected]]
Sent: Wednesday, February 03, 2010 12:00 PM
To: [email protected]
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)
<[email protected]>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:[email protected]
P.S. A good JDO? O, Gasp!
Craig L Russell
Architect, Sun Java Enterprise System http://db.apache.org/jdo
408 276-5638 mailto:[email protected]
P.S. A good JDO? O, Gasp!
