title: JPA criteria builder with multiple joins
body: <p>i'm struggling to create a jpa query that makes use of several tables. i cannot seem to understand how to join the tables together. this is the query i am trying to create:</p>
  
  <pre><code>SELECT algm.m_l_i, algnsm.n_s_i
    FROM algm, alg, algnsm, mal
   WHERE algm.l_g_i = alg.l_g_i
     AND alg.l_g_t = 'xxx'
     AND algnsm.l_g_i = algm.l_g_i
     AND mal.c_l_i = algm.m_l_i
     AND mal.p_l_i = 'yyy';
  </code></pre>
  
  <p>i have tried several approaches, from using the join operator to using the where operator on the keys. i cannot find enough info on joins on the net to help much. i get stuck as soon as i try to join more than 1 table deep. some advice or tips would really be useful. 
  thanks </p>
  
  <pre><code>CriteriaBuilder b = em.getCriteriaBuilder();
  CriteriaQuery&lt;Tuple&gt; q = builder.createTupleQuery();
  Root&lt;MAL&gt; malRoot = query.from(MAL.class);
  Root&lt;ALGM&gt; algmRoot = query.from(ALGM.class);
  
  // error
  algmRoot.join(ML_.mLI);
  // error
  malRoot.join(ML_.mLI);
  
  Predicate e1 = builder.equal(malRoot.get(MAL_.pL).get(ML_.mLI), "abc");
  Predicate e2 = builder.equal(malRoot.get(MAL_.cL), algmRoot.get(ALGM_.mL));
  
  query.where(builder.and(e1, e2));
  
  query.select(builder.tuple(malRoot.get(MAL_.pL), malRoot.get(MAL_.cL), algmRoot.get(ALGM_.aLG).get(ALG_.lGT)));
  
  @Entity
  @Table(name = "M_A_L")
  public class MAL implements Serializable {
     @EmbeddedId
     protected MALPK malPK;
  
     @Basic(optional = false)
     @Column(name = "ENTRY_IND")
     private String entryInd;
  
     @JoinColumn(name = "P_L_I", referencedColumnName = "M_L_I", insertable = false, updatable = false)
     @ManyToOne(optional = false)
     private ML pL;
  
     @JoinColumn(name = "C_L_I", referencedColumnName = "M_L_I", insertable = false, updatable = false)
     @ManyToOne(optional = false)
     private ML cL;
   ... 
   }
  
  @Entity
  @Table(name = "A_L_G_M")
  public class ALGM implements Serializable {
  
      @EmbeddedId
      protected ALGMPK algmPK;
  
      @JoinColumn(name = "M_L_I", referencedColumnName = "M_L_I", insertable = false, updatable = false)
      @ManyToOne(optional = false, fetch = FetchType.EAGER)
      private ML mL;
  
      @JoinColumn(name = "L_G_I", referencedColumnName = "L_G_I", insertable = false, updatable = false)
      @ManyToOne(optional = false)
      private ALG aLG;
   ....
   }
  
  public class ALGM_ { 
     public static volatile SingularAttribute&lt;ALGM, ML&gt; mL;
     public static volatile SingularAttribute&lt;ALGM, ALGMPK&gt; aLGMPK;
     public static volatile SingularAttribute&lt;ALGM, ALG&gt; aLG;
  }
  
  public class MAL_ { 
     public static volatile SingularAttribute&lt;MAL, String&gt; eI;
     public static volatile SingularAttribute&lt;MAL, MALPK&gt; mALPK;
     public static volatile SingularAttribute&lt;MAL, ML&gt; pL;
     public static volatile SingularAttribute&lt;MAL, ML&gt; cL;
  }
  
  public class ALG_ { 
     public static volatile CollectionAttribute&lt;ALG, MW&gt; mWC;
     public static volatile SingularAttribute&lt;ALG, ALGCM&gt; aLGCM;
     public static volatile SingularAttribute&lt;ALG, ALGAVM&gt; aLGAVM;
     public static volatile SingularAttribute&lt;ALG, TR&gt; tZ;
     public static volatile CollectionAttribute&lt;ALG, ALGM&gt; aLGMC;
     public static volatile SingularAttribute&lt;ALG, String&gt; d;
     public static volatile SingularAttribute&lt;ALG, String&gt; lGT;
     public static volatile SingularAttribute&lt;ALG, String&gt; lGI;
     public static volatile SingularAttribute&lt;ALG, ALGNSM&gt; aLGNSM;
     public static volatile SingularAttribute&lt;ALG, ALGFVM&gt; aLGFVM;
     public static volatile SingularAttribute&lt;ALG, ALGNAM&gt; aLGNAM;
  }
  </code></pre>