CODEWITHSUNDEEP
hibernatejpaspring-data-jpa
wannabeartist
wannabeartist

Reputation: 2833

Correct way to query an entity with nested collections?

It looks like basic queries don't work when collection elements have collections of their own.

Imagine a data model for a bank with customers, which have portfolios, which have investments. What is the correct way to get customers?

I tried this:

@Query("SELECT DISTINCT c FROM Customer c LEFT JOIN FETCH c.portfolios")

But it fails with "Failed to lazily initialize a collection of role".

Investments is defined as:

@ManyToMany(cascade = CascadeType.ALL)

The query will work, if I change the fecthType:

@ManyToMany(cascade = CascadeType.ALL, fetch=FetchType.EAGER)

But is there a way to make it work without changing the fetchtype?

EDIT:

I should mention that I used Spring's JpaRepository to define the above query. All entities have their respective repositories, but it looks like fetching Customers does not touch any methods in Portfolios repository (which would LEFT JOIN the Investments) and so the investments are never fetched.

Also, I can fetch a Portfolio with a similar query and it works fine as Investment does not have any collections. But fetching the chain customer -> portfolios -> investments fails.

Upvotes: 1

Views: 2632

Answers (1)

JB Nizet
JB Nizet

Reputation: 691685

Your query selects customers and fetches their portfolios, using a left join fetch. If you want to also fetch the invstments of the portfolios, you need an additional join fetch, just like in SQL:

select distinct c FROM Customer c 
left join fetch c.portfolios portfolio
left join fetch portfolio.investments

Upvotes: 2

Related Questions