Spring Data Specification: notEquals() in relationship

Question

I have several entitites:

TranslationUnit
TranslationValue
Locale

TranslationUnit has relationship ManyToMany with TranslationValue.
TranslationValue has 1-1 with Locale.
Locale has meaningful field - jhi_value;
What I need to do is: Select those TranslationUnits, that:

1) do not have related TranslationValues (which means - do not have translation at all) 2) do not have TranslationValues with specific Locale (e.g, "en").

I've built SQL query for that, works perfectly in my Postgres DB:

select * from translation_unit tu where  not exists ( select null from translation_value tv join locale l on tv.locale_id =l.id  where l.jhi_value='en'  and tu.id=tv.translation_unit_id);

I need to build Specification for this (it will be combined with other existing Specifications).

Appreciate any help.

Answer 1

Found the solution:

Subquery<TranslationValue> subquery = q.subquery(TranslationValue.class);
                Root<TranslationValue> from = subquery.from(TranslationValue.class);
                subquery.distinct(true).
                    select(from).where(cb.and(
                    cb.equal(from.get(TranslationValue_.LOCALE).get(Locale_.VALUE), filterRequest.getLanguageCode())),
                    cb.equal(r.get(TranslationUnit_.ID), from.get(TranslationValue_.TRANSLATION_UNIT))