title: JPA2, LEFT JOIN, Criteria API
body: <p>I have the following situation SQL that I need to implement into my criteria builder for dynamic query creation.</p>
  
  <pre><code>select t1.ticketnr 
  from tbl_ticket t1 
  left join tbl_tickets_updates t2 on t1.ticketnr = t2.ticketnr 
  where t1.description ilike '%EXAMPLE%' or t2.description ilike '%EXAMPLE%';
  </code></pre>
  
  <p>In my code I have the following:</p>
  
  <p>tbl_ticket = Tickets.class
  PK = ticketnr</p>
  
  <p>tbl_tickets_updates = TicketsUpdates.class and TicketsUpdatesPK.class
  PK = ticketnr, updatedby, timeofupdate</p>
  
  <pre><code>        CriteriaBuilder builder = em.getCriteriaBuilder();
          CriteriaQuery&lt;Tickets&gt; query = builder.createQuery(Tickets.class);
          EntityType&lt;Tickets&gt; type = em.getMetamodel().entity(Tickets.class);
          Root&lt;Tickets&gt; root = query.from(Tickets.class);
          List&lt;Predicate&gt; predicatesAnd = new ArrayList&lt;Predicate&gt;();
  ...
      if (text.length() != 0) {
                      predicatesAnd.add(builder.or(
                              builder.like(
                              builder.lower(
                              root.get(
                              type.getDeclaredSingularAttribute("description", String.class))), "%" + text.toLowerCase() + "%"),
                              builder.like(
                              builder.lower(
                              root.get(
                              type.getDeclaredSingularAttribute("summary", String.class))), "%" + text.toLowerCase() + "%")));
                      Join&lt;Tickets, TicketsUpdates&gt; tupdates = root.join("ticketnr", JoinType.LEFT);
                      predicatesAnd.add(builder.like(builder.lower(tupdates.get("description").as(String.class)), "%" + text.toLowerCase() + "%"));
                  }
  </code></pre>
  
  <p>My code is failing at the Left Join with: <strong>java.lang.IllegalStateException: CAN_NOT_JOIN_TO_BASIC</strong> </p>
  
  <p>I believe this is happening due to having a separate class for the composite PK, but so far I have not found a way to implement this properly.</p>
  
  <p>Any ideas?</p>