Join on different elements using Spring JPA Specifications and Hibernate

Question

I'm trying to build a JPA specification for a I18N implementation to be able to filter on a name.
In the database I have translations for multiple languages.
The thing is that most of the times only the default language is specified.
So when someone requests the translation for a non default language I want it to fall back on the default language.

Until now I was able to build this in Specifications

Specification<S> specification = (root, query, cb) -> {
  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
};

But this causes an error which sounds like bad news for this solution

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 )
];

So Hibernate doesn't allow me to build a with-clause with different elements (in this case itemId and languageId).
Is there any way how I can implement this correctly or in a different way?

In the end I'd like Hibernate to generate a query (Oracle) that looks like this

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;

Solving this using a where clause

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).

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.

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 like '%a%' it would return 150 results and when I flip the filter (not like '%a%') it would return something like 30. Where I would expect them to add up to 200.

This works

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%'))
)

This does not work (doesn't return the correct amount of elements)

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%'))
)

Is there a way to implemented the query, that works, using JPA specifications?

Answer 1

After a weekend of research I found out that Hibernate 5.1.0 packs a new feature that allows you to build a join using criteria of multiple columns/fields. It's called cross-join (see comments).

See this ticket and this commit.

I asked around on the Spring Data Gitter about when this will be available for use with Spring Boot 1.x. They pointed me to setting the hibernate.version property in my maven or gradle file. Hence this only seems to work when you use the org.springframework.boot gradle plugin.

apply plugin: "org.springframework.boot"

Because I use Gradle I ended up adding the following to my build.gradle

ext {
    set('hibernate.version', '5.1.0.Final')
}

Or when using a gradle.properties file you can just put this line in there

hibernate.version=5.1.0.Final

And finally I'm able to do this

Specification<S> specification = (root, query, cb) -> {
  Join i18nJoin = root.join(collectionName, JoinType.LEFT);
  Join i18nDefaultJoin = root.join(collectionName, JoinType.LEFT);

  i18nJoin.on(cb.equal(i18nJoin.get("languageId"), 22));
  i18nDefaultJoin.on(cb.equal(i18nDefaultJoin.get("languageId"), 1));

  ... where clause and return ...
}

Which results in the following query

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 i18n_default.language_id = 1

Notice that using the on method doesn't overwrite the original clause set by the association annotation (in this case @OneToMany), but extends it with your own criteria.