> -----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. :) 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!
