title: How to do JPQL with JOIN FETCH to collect the multiple JOINs in one query
body: <p>How to write JPQL with JOIN FETCH to grab all the <strong>Post</strong> collection and associated tags &amp; items &amp; subitems in one call without N+1 query from database.</p>
  <p>Entities</p>
  <pre><code>@Entity
  public class Post {
    @Id
    private String postId;
    private String postName;
    @OneToMany(mappedBy = &quot;Post&quot;, cascade = CascadeType.ALL)
    private Collection&lt;PostTag&gt; postTags = new HashSet&lt;PostTag&gt;();
  }
  @Entity
  public class Tag {
    @Id
    private String tagId;
    private String tagName;
    @OneToMany(mappedBy = &quot;tag&quot;, cascade = CascadeType.ALL)
    @JsonIgnore
    private Collection&lt;PostTag&gt; postTags = new HashSet&lt;PostTag&gt;();
  }
  @Entity
  public class PostTag {
    @EmbeddedId
    private PostTagId postTagId = new PostTagId();
    
    @ManyToOne(fetch = FetchType.LAZY)
    @MapsId(&quot;postId&quot;)
    @JoinColumn(name = &quot;post_Id&quot;)
    @JsonIgnore
    private Post post;
    
    @ManyToOne(fetch = FetchType.LAZY)
    @MapsId(&quot;tagId&quot;)
    @JoinColumn(name = &quot;tag_Id&quot;)
    private Tag tag;
    
    @OneToMany(mappedBy = &quot;posttag&quot;, cascade = CascadeType.ALL)
    @JsonIgnore
    private Set&lt;Items&gt; items= new HashSet&lt;Items&gt;();
  
    private String someDateField;
  }
  @Embeddable
  public class PostTagId implements Serializable {
    private String postId;
    private String tagId;
    //equals &amp; hashcode ommited
  }
  public class Items{
    @Id
    private String itemId;
    private String itemName;
  
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumns({@JoinColumn(name = &quot;post_id&quot;), @JoinColumn(name = &quot;tag_id&quot;)})
    @JsonBackReference
    @JsonIgnore
    private PostTag postTag;
  
    @OneToMany(mappedBy = &quot;items&quot;, cascade = CascadeType.ALL)
    @JsonIgnore
    private Set&lt;SubItems&gt; subItems= new HashSet&lt;SubItems&gt;();
  
  }
  public class SubItems{
    @Id
    private String subItemId;
    private String subItemName;
  
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumns({@JoinColumn(name = &quot;itemId&quot;)})
    @JsonBackReference
    @JsonIgnore
    private Items items;
  
  }
  </code></pre>
  <p>JPA findAll() triggers so many join queries which is 1 query per result per table. for example if 1 association is there in all the table 4 total queries being triggered.</p>
  <p>Have tried,</p>
  <pre><code>@Query(&quot;select po from Post po inner join fetch PostTag pt on po.postId=pt.post.postId inner join fetch Tag t on pt.tag.tagId = t.tagId&quot;)
  Page&lt;Post&gt; findAllInOneQuery(Pageable pageable);
  </code></pre>
  <p>But this results in exception. what is the right way to get all the collections in one shot using JOIN FETCH with @Query command?</p>
  <p><strong>Note:</strong>
  Plain findAll() triggers bellow queries for 1 result with all association.</p>
  <pre><code>Hibernate: select post0_.postId as postid1_0_, post0_.postName as postname2_0_ from post post0_ inner join PostTag posttag1_ on post0_.postId=posttag1_.post_id inner join tagInfo taginfo2_ on posttag1_.tag_id=taginfo2_.tagId left outer join PostTag posttag3_ on post0_.postId=posttag3_.post_id where (taginfo2_.tagName like ?) and (posttag1_.someDateField between ? and ?) order by posttag3_.someDateField desc, posttag3_.pubDate desc offset 0 rows fetch next ? rows only
      
      Hibernate: select posttag0_.post_id as post_id1_4_0_, posttag0_.tag_id as tag_i2_4_0_, posttag0_.post_id as post_id1_4_1_, posttag0_.tag_id as tag_i2_4_1_, posttag0_.content as content3_4_1_, posttag0_.someDateField as someDateField10_4_1_ from PostTag posttag0_ where posttag0_.post_id=?
      
      Hibernate: select items0_.post_id as post_id3_1_0_, items0_.tag_id as tag_i4_1_0_, items0_.productId as producti1_1_0_, items0_.productId as producti1_1_1_, items0_.productName as productn2_1_1_, items0_.post_id as post_id3_1_1_, items0_.tag_id as tag_i4_1_1_ from Items items0_ where items0_.post_id=? and items0_.tag_id=?
      
      Hibernate: select subitems0_.productId as producti3_3_0_, subitems0_.subitemId as subitemi1_3_0_, subitems0_.subitemId as subitemi1_3_1_, subitems0_.productId as producti3_3_1_, subitems0_.subitemName as subitemn2_3_1_ from SubItems subitems0_ where subitems0_.productId=?
      
      Hibernate: select count(post0_.postId) as col_0_0_ from post post0_ inner join PostTag posttag1_ on post0_.postId=posttag1_.post_id inner join tagInfo taginfo2_ on posttag1_.tag_id=taginfo2_.tagId where (taginfo2_.tagName like ?) and (posttag1_.someDateField between ? and ?)
      
      Hibernate: select taginfo0_.tagId as tagid1_2_0_, taginfo0_.tagName as tagna2_2_0_ from tagInfo taginfo0_ where taginfo0_.tagId=?
  </code></pre>
  <p>How can i convert the above queries into one single JOIN fetch using @Query!!!!</p>
  <p><strong>Solutions tried so far!!</strong> (Not working)</p>
  <pre><code>@Query(&quot;select po.postId as postid1_0_, po.postName as postname2_0_ from post po inner join fetch PostTag pt1 inner join fetch tagInfo t left outer join fetch PostTag pt2 where (t.tagName like :tName) and (pt1.someDateField between :sDate and :eDate) order by pt2.someDateField desc&quot;)
  Page&lt;Post&gt; findAllInOneQuery(String tName, String sDate, String eDate, Pageable pageable);
  </code></pre>
  <p><strong>Exception:</strong></p>
  <pre><code>illegal attempt to dereference collection [po.postId.posttag] with element property reference [someDateField]
  </code></pre>