JPA Fetch Join Filter on List/Set

Question

I'm having problem on JPA (Hibernate) Fetch Join :

Here is my JPQL query

SELECT n FROM News n LEFT JOIN FETCH n.profiles AS pr WHERE pr.id=?1

But it's not working. How can I make a query that filters on the list that is being fetched ?

Answer 1

According to the specification, what you're trying to do is not allowed (at least not by JPA). From the JPA 1.0 specification:

4.4.5.3 Fetch Joins

A FETCH JOIN enables the fetching of an association as a side effect of the execution of a query. A FETCH JOIN is specified over an entity and its related entities.

The syntax for a fetch join is

fetch_join ::= [ LEFT [OUTER] | INNER ] JOIN FETCH join_association_path_expression

The association referenced by the right side of the FETCH JOIN clause must be an association that belongs to an entity that is returned as a result of the query. It is not permitted to specify an identification variable for the entities referenced by the right side of the FETCH JOIN clause, and hence references to the implicitly fetched entities cannot appear elsewhere in the query.

So the following should work:

SELECT n FROM News n LEFT JOIN n.profiles p WHERE p.id = ?1

But you can't use a FETCH JOIN here.