Two level fetch join

[David Smalley]

Hi,

I am having a difficult time getting a single query to populate two levels of collections. Maybe I am missing something, or maybe it isn't possible.

Imagine the following model: A Customer object with a OneToMany relationship to an Order object with a ManyToMany relationship to Product objects. (This isn't realistic, because the products in an Order typically have order-specific information, like quantity, but it will do for this question).

I want to retrieve the entire object tree in a single query. I have been trying to make it work with fetchJoin() (I am using QueryDSL 4.0.7), and getting nowhere. If I write a queries like:

List<Customer> customers = JPAQueryFactory.selectFrom(c).leftJoin(c.orders).fetchJoin().fetch;
List<Order> orders = JPAQueryFactory.selectFrom(o).leftJoin(o.products).fetchJoin().fetch;

they do retrieve what I expect, a list of all parent objects with the collection populated. But I can't seem to combine the two queries so that I can get a list of all customers with their order collection populated, and each order with its product collection populated. I won't bother showing what I've tried, because they all fail, either with a lazy initialization error, or freezing the application.

BTW, I've tried with JPAQuery's and HibernateQuery's, and the results are the same.

If I make the products collection eagerly loaded, the first query works (big deal). Also, if I do the brute force method of iterating the collections, it works, but of course generates multiple queries. The only other workaround is to use EclipseLink, and depend on its "feature" of lazy loading collections outside of a transaction or even a persistence context. I hate that, and anyway, it doesn't accomplish what I want because it also generates multiple queries.

So, am I trying to do the impossible, or have I just missed something?

Dave

[timowest]

Hi.

Did you also try

List<Customer> customers = queryFactory.selectFrom(c)

    .leftJoin(c.orders, o).fetchJoin()

    .leftJoin(o.products, o).fetchJoin().fetch();

Timo

[timowest]

Sorry, it should be

List<Customer> customers = queryFactory.selectFrom(c)

    .leftJoin(c.orders, o).fetchJoin()

    .leftJoin(o.products, p).fetchJoin().fetch();

[David Smalley]

Hi Tim,

Thanks for the answer; sorry I didn't respond quicker.

My mistake was leaving out the alias for product, in other words, I was trying:

List<Customer> customers = queryFactory.selectFrom(c)

    .leftJoin(c.orders, o).fetchJoin()

    .leftJoin(o.products).fetchJoin().fetch();

I guess I was foolishly assuming that because I didn't intend to use the products further in the query (such as say: ...where(p.name.eq("stuff")...), I didn't need to provide an EntityPath<Product> for the third entity. Obviously, adding it makes everything work fine.

Dave