Spring Data 'Left Join Fetch' query returning null

Question

My query looks like this:

@Query("SELECT p FROM Pilot p LEFT JOIN FETCH p.playerShips WHERE p.nickname = (:nickname)")

So far so good. I'm getting Pilot instance even when playerShips is empty.
Now, I want to fetch only inactive ships so I modified my query to look like this:

@Query("SELECT p FROM Pilot p LEFT JOIN FETCH p.playerShips s WHERE p.nickname = (:nickname) AND s.active = false")

and I'm getting null as a pilot so it clearly doesn't work.
I'd be glad if someone could explain me how to create a JOIN FETCH query with WHERE clause that applies to the child elements. Thanks in advance.

Answer 1

Just move it to the join condition:

@Query("SELECT p FROM Pilot p LEFT JOIN FETCH p.playerShips s ON s.active = false WHERE p.nickname = (:nickname)")

The ON clause is defined in the JPA 2.1

Answer 2

After a lot of research, I decided to implement a custom repository so that I can use Hibernate Filters and now it works.

PilotRepository:

@Repository
public interface PilotRepository extends JpaRepository<Pilot, Long>, PilotRepositoryCustom{
/*spring data methods here*/
}

PilotRepositoryCustom:

public interface PilotRepositoryCustom {
    public Pilot findByNicknameFetchInactiveShips(String nickname);
}

PilotRepositoryImpl:

public class PilotRepositoryImpl implements PilotRepositoryCustom{

    @PersistenceContext
    EntityManager entityManager;

    @Override
    public Pilot findByNicknameFetchInactiveShips(String nickname) {
        Session session = entityManager.unwrap(Session.class);
        session.enableFilter("active").setParameter("active", false);
        Query query = entityManager.createQuery(
                "SELECT p FROM Pilot p " +
                "LEFT JOIN FETCH p.playerShips ps " +
                "LEFT JOIN FETCH ps.ship s " +
                "WHERE p.nickname = (:nickname)");
        query.setParameter("nickname", nickname);
        return (Pilot)query.getSingleResult();
    }
}

That's it about spring data. Now the Filters configuration:

Pilot Entity:

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "pilot")
    @Filter(name="active", condition = "active = :active")
    private List<PlayerShip> playerShips;

PlayerShip Entity:

@Entity
@Table(name="player_ship")
@FilterDef(name="active", parameters=@ParamDef(name="active",type="java.lang.Boolean"))
public class PlayerShip {
    /*...*/
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "pilot_id")
    private Pilot pilot;
    /*...*/
}

IMPORTANT :
If you use Boolean in @ParamDef, make sure to type java.lang.Boolean instead of just Boolean. I've spent more than one hour wondering why I keep getting parameter not found/not defined error.