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 ?
According to the specification, what you're trying to do is not allowed (at least not by JPA). From the JPA 1.0 specification:
18.104.22.168 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.
Try this way:
SELECT n FROM News n LEFT JOIN FETCH n.profiles AS pr WITH (pr.id=?1)