JPQL Query for page of children and join fetch of grandchildren throws exception query specified join fetching

Question

I have entities:

class Parent {
  ...
  @OneToMany(Fetch = FetchType.EAGER)
  @OrderBy("id")
  private List<Child>  children;
}


class Child{
  ...
  @OneToMany(Fetch = FetchType.LAZY)
  @OrderBy("id")
  private List<GrandChild> grandChildren;
}

class GrandChild {
  ...
}

I want to query for paginated childs and also fetch grand childrens. I also filter childrens by name, but I don't think it's important. I have such JPQL query:

@Query("select c " +
       "from Parent p " +
       "inner join p.children c " +
       "inner join fetch c.grandchildren g " +
       "where p = :parent and " +
       "c.childName like concat(:childName,'%')")
    Page<Expense> findBy(@Param("parent") Parent parent, @Param("childName")String childName, Pageable pageable);

And during starting spring boot application exception is thrown:

query specified join fetching, but the owner of the fetched association was not present in the select list

Message is straightforward, however i can't add parent to select statement cause i want only children.

score 2 · Answer 1 · answered Apr 17 '19 at 12:20

2

c.grandchildren is missing the alias e

@Query("select e " +
       "from Parent p " +
       "inner join p.children c " +
       "inner join fetch c.grandchildren e" +
       "where p = :parent and " +
       "c.childName like concat(:childName,'%')")
    Page<Expense> findBy(@Param("parent") Parent parent, @Param("childName")String childName, Pageable pageable);

answered Apr 17 '19 at 12:20

Simon Martinelli

34,053
5
48
82

Sorry, my fault i query for children not grandchildren, I have edited my question. – Kacper Apr 17 '19 at 15:37

score 1 · Accepted Answer · answered Apr 17 '19 at 21:10

I found out that when you want to get page of data you can't use join fetch so according to this question. I have created two methods in JpaRepository.

Method to retrieve page of child ids
Method to retrieve children with fetched grandchildre.

This is how code looks like after that:

@Query("select c.id " +
  "from Parent p " +
  "inner join p.children c " +
  "where p = :parent and " +
  "c.childrenName like concat(:childrenName,'%')")
Page<Long> findIdsBy(@Param("parent") Parent parent,
  @Param("childrenName") String childrenName,
  Pageable pageable);

@Query("from Children c join fetch c.grandChildren where c.id in :ids")
List<Children> getChildrenByIdWithGrandChildren(@Param("ids") List<Long> ids);

JPQL Query for page of children and join fetch of grandchildren throws exception query specified join fetching

2 Answers2