> -----Original Message----- > From: KARR, DAVID (ATTCINW) > Sent: Wednesday, February 03, 2010 11:03 AM > To: [email protected] > Subject: JPQL to get association members, but only ones that fulfill a > condition > > I have a query that gets an entity and joins with its child > associations, but I'm now realizing that I have to filter the entities > in the child associations to ones that fulfill a condition. I looked > through the openjpa doc, but I didn't see an example that looks like > this. > > For example, my main entity is "Foo", which has a "one-to-many" for > "Bar" called "childBars". > > My initial query was this: > > "select foo from packagepath.Foo foo left join fetch foo.childBars > where foo.id=:id". > > I now need to do something like this: > > "select foo from packagepath.Foo foo left join fetch foo.childBars > where foo.id=:id and > current_date between foo.childBars.startDate and > foo.childBars.endDate"
Hmm. I thought I had found a solution in the JPA spec, that had an example like this: SELECT DISTINCT o FROM Order AS o JOIN o.lineItems AS l WHERE l.shipped = FALSE 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 However, this fails with: Encountered "as" at character .., but expected: [",", ".", "GROUP", "HAVING", "INNER", "JOIN", "LEFT", "ORDER", "WHERE", <EOF>].
