Hibernate HQL nested query

Question

I've three classes that have nested relationships:

public class Route {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @ManyToMany(cascade = {CascadeType.DETACH, CascadeType.REFRESH}, fetch = FetchType.EAGER)
    private List<Place> places;
}

public class Place {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @OneToMany(cascade = {CascadeType.DETACH, CascadeType.REFRESH, CascadeType.REMOVE}, mappedBy = "place")
    private List<Image> images;
}

public class Image {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @ManyToOne(cascade = {CascadeType.REFRESH, CascadeType.DETACH})
    private Place place;

}

I need to fetch a pageable list of images by a route id.

Thats my query:

@Repository
public interface ImageRepository extends CrudRepository<Image, Long> {
    @Query("select place.images from Place place where place.id in (select placeD.id from Place placeD where placeD in (select route.places from Route route where route.id = :routeId)) ")
    Page<List> findAllByRouteId(@Param("routeId") Long routeId, Pageable Page);
}

I've tried different combinations but still no result. I'm new to HQL and read a lot of tutorials and as I understood it requires me to write a connected query as it is now.

But it doesn't work. An error it throws:

InvalidDataAccessResourceUsageException: could not prepare statement

How can I fix it? Or at least where should I dig to?

Answer 1

Try:

@Query("SELECT DISTINCT i 
        FROM Route AS r
           LEFT JOIN r.places AS p
           LEFT JOIN p.images AS i
        WHERE r.id = :routeId)) ")