I am trying to set a default value in Spring Data projection when value has not been found after JOIN and WHERE clause. Let's assume I have Product with optional Status. Statuses are translated into multiple Languages in order to meet i18n requirements. Below is simplified DB example diagram but sufficient enough to visualize my issue I guess.
And this is what I have tried so far:
@Query(value =
" SELECT p.id AS productId," +
" p.barCode AS barCode," +
" COALESCE(pd.productStatus, 'translation not found') AS productStatus " +
" FROM ProductEntity p" +
" LEFT JOIN p.productStatusEntities pd" +
" LEFT JOIN pd.languageEntity l" +
" WHERE (l IS NULL OR l.langName = :langName)" +
" AND p.barCode IN (:barCodes)" +
" ORDER BY pd.productStatus")
Collection<ProductProjection> findProductsByBarCodes(@Param("barCodes") Collection<String> barCodes,
@Param("langName") String langName);
The problem is: when the translation is missing then entire row (product) is omitted (probably because of JOIN and WHERE clause). All I want is to return products with its status if translation exists, otherwise translation not found as status value, example response:
| Product ID | Bar code | Status |
|---|---|---|
| 1 | 12345 | Some translated status 1 |
| 2 | 56789 | (null - status not set) |
| 3 | 54321 | Translation not found |
It's important to point out that I can't just return null status because it will be impossible to determine whether status has not been set yet or translation is missing. Furthermore, I can't fetch translations outside repository because I need to sort by statuses with pagination mechanism.
I have tried different query combinations and SO answers but with no success. Can anyone give me advice how can I solve my problem or point me out some reference? I am using JPQL with PostgreSQL but native SQL will be appreciated as well.
