title: Join on different elements using Spring JPA Specifications and Hibernate
body: <p>I'm trying to build a JPA specification for a I18N implementation to be able to filter on a name.<br/>
  In the database I have translations for multiple languages.<br/>
  The thing is that most of the times only the default language is specified.<br/>
  So when someone requests the translation for a non default language I want it to fall back on the default language.<br/></p>
  
  <p>Until now I was able to build this in Specifications</p>
  
  <pre><code>Specification&lt;S&gt; specification = (root, query, cb) -&gt; {
    Join i18n = root.join("translations", JoinType.LEFT);
    i18n.alias("i18n");
    Join i18nDefault = root.join("translations", JoinType.LEFT);
    i18nDefault.alias("i18nDefault");
  
    i18n.on(cb.and(cb.equal(i18n.get("itemId"), root.get("itemId")), cb.equal(i18n.get("languageId"), 1)));
    i18nDefault.on(cb.and(cb.equal(i18nDefault.get("itemId"), root.get("itemId")), cb.equal(i18nDefault.get("languageId"), 22)));
  
    // Clauses and return stuff
  };
  </code></pre>
  
  <p>But this causes an error which sounds like bad news for this solution</p>
  
  <pre><code>org.hibernate.hql.internal.ast.QuerySyntaxException: with-clause referenced two different from-clause elements
  [
   select generatedAlias0 from com.something.Item as generatedAlias0 
   left join generatedAlias0.i18n as i18n with (i18n.itemId=generatedAlias0.itemId) and ( i18n.languageId=1L )
   left join generatedAlias0.i18n as i18nDefault with (i18nDefault.itemId=generatedAlias0.itemId) and ( i18nDefault.languageId=1L )
  ];
  </code></pre>
  
  <p>So Hibernate doesn't allow me to build a with-clause with different elements (in this case itemId and languageId).<br/>
  <strong>Is there any way how I can implement this correctly or in a different way?</strong></p>
  
  <p>In the end I'd like Hibernate to generate a query (Oracle) that looks like this</p>
  
  <pre><code>SELECT *
  FROM item i
  LEFT JOIN i18n_item i18n ON i.item_id = i18n.item_id AND i18n.language_id = 22
  LEFT JOIN i18n_item i18n_default ON i.item_id = i18n_default.item_id AND i18n_default.language_id = 1
  // where-clauses;
  </code></pre>
  
  <p><br/>
  <h3>Solving this using a where clause</h3><br/>
  I've seen a few other related questions that all have answers that say to use a where clause instead of a second join criteria.
  The thing is that the translations for the language that is not default may be missing for some records of the item. As the users are the ones that specify the translations for their content (which they also manage).</p>
  
  <p>For example, The user might have specified 200 translation records for the items that are meant for English, but only 190 translation records for the items that are meant for Dutch. English is the default language in the example above.</p>
  
  <p>I tried to build a query using a where clause, but that caused the amount of results to be inconsistent. By which I mean that unfiltered result would be 200 items. When I filter on name using <code>like '%a%'</code> it would return 150 results and when I flip the filter (<code>not like '%a%'</code>) it would return something like 30. Where I would expect them to add up to 200.</p>
  
  <p><strong>This works</strong></p>
  
  <pre><code>SELECT *
  FROM item i
  LEFT JOIN i18n_item i18n ON i.item_id = i18n.item_id AND i18n.language_id = 22
  LEFT JOIN i18n_item i18n_default ON i.item_id = i18n_default.item_id AND i18n_default.language_id = 1
  WHERE 
  (
    (lower(i18n.name) not like '%a%') 
  or 
    (i18n.name is null and (lower(i18n_default.name) not like '%a%'))
  )
  </code></pre>
  
  <p><strong>This does not work (doesn't return the correct amount of elements)</strong></p>
  
  <pre><code>SELECT *
  FROM item i
  LEFT JOIN i18n_item i18n ON i.item_id = i18n.item_id
  LEFT JOIN i18n_item i18n_default ON i.item_id = i18n_default.item_id
  WHERE 
  (
    (i18n.language_id = 22 and lower(i18n.name) not like '%a%') 
  or 
    (i18n_default.language_id = 1 and i18n.name is null and (lower(i18n_default.name) not like '%a%'))
  )
  </code></pre>
  
  <p><strong>Is there a way to implemented the query, that works, using JPA specifications?</strong></p>