Hi David, It is not clear to me that "select foo from Device dev, in(dev.childFoos) foo" (1) did not give you anything. If any Device has one or more Foos, the query (1) should return a list of Foos for all Devices. If Foo has an inverse ToOne relation to Device, there should be no duplicates returned from the following query "select foo from Device dev left join dev.childFoos, in(dev.childFoos) foo" (2) The difference between query (1) and (2) is that (1) returns all Foos that belong to a Device; while (2) returns not only those Foos that belong to a Device but also those Foos that do not belong to any Device.
An equivalent query string: "select foo from Device dev left join dev.childFoos foo" returns the same result as (2). Catalina On Fri, May 28, 2010 at 4:05 PM, KARR, DAVID (ATTSI) <[email protected]> wrote: > I could some help building a JPA query that isn't quite as trivial as > the ones I've had to build before. > > I have a "Product" entity, with some subclasses, including "Device". > The "Product" entity has a "childFoos" one-to-many list, whose element > type is "Foo". > > I need to get a list of all the unique "Foo" objects in the "childFoos" > list for all "Device" objects. > > One guess might be "select dev.childFoos from Device dev". This fails > with "Query projections cannot include array, collection, or map > fields.". > > After looking at more examples in the OpenJPA doc, I tried "select foo > from Device dev, in(dev.childFoos) foo". This didn't fail, but it > didn't give me anything, either. At this point, I assumed I needed a > "fetch join" in here somewhere. As a result, I tried "select foo from > Device dev left join fetch dev.childFoos, in(dev.childFoos) foo". > > This appeared to work. Is that the best way to do this? Do I need to > add "distinct" to this if there could be duplicates in the union? > > As this returns a relatively large list, I took the advice in the "Large > Result Set Proxies" section and iterated through the list, calling > "OpenJPAPersistence.close(iter)" in the finally block. >
