COALESCE with HQL

Hibernate ORM
1

Is it possible to use COALESCE / NVL with selected entities inside HQL queries?

@Query(
	SELECT DISTINCT COALESCE(  
		pi.provider.address.city.country, 
		pi.provider.address.alternateCountry 
	)  
	FROM PriceInfo pi 
	LEFT JOIN pi.article.categories cat 
	WHERE 
		pi.provider.objectStatus = :providerStatus  AND 
		pi.article.objectStatus = :articleStatus  AND 
		cat.objectStatus = :categoryStatus  AND  
		( 
			( 
				pi.provider.address.city.objectStatus = :cityStatus   AND 
				pi.provider.address.city.country.objectStatus = :countryStatus 
			) 
			OR  ( 
				pi.provider.address.city IS NULL   AND 
				pi.provider.address.alternateCountry IS NOT NULL   AND 
				pi.provider.address.alternateCountry.objectStatus
					= :countryStatus 
			)
		) 
		
)
List<Country> havingArticles(
    @Param("providerStatus") ObjectStatus providerStatus,
    @Param("countryStatus")  ObjectStatus countryStatus,
    @Param("cityStatus")     ObjectStatus cityStatus,
    @Param("articleStatus")  ObjectStatus articleStatus,
    @Param("categoryStatus") ObjectStatus categoryStatus
);

The working version is here but it’s twice ugly as the one with COALESCE:

SELECT DISTINCT pi.provider.address.city.country  
FROM PriceInfo pi 
LEFT JOIN pi.article.categories cat 
WHERE 
	pi.provider.objectStatus = :providerStatus 
	AND 
	pi.article.objectStatus = :articleStatus 
	AND 
	cat.objectStatus = :categoryStatus 
	AND  	
	pi.provider.address.city.objectStatus = :cityStatus 
	AND 
	pi.provider.address.city.country.objectStatus = :countryStatus 
	
UNION 

SELECT DISTINCT pi.provider.address.alternateCountry  
FROM  PriceInfo pi 
LEFT JOIN pi.article.categories cat 
WHERE 
	pi.provider.objectStatus = :providerStatus 
	AND 
	pi.article.objectStatus = :articleStatus 
	AND cat.objectStatus = :categoryStatus 
	AND  
	pi.provider.address.city IS NULL 
	AND 
	pi.provider.address.alternateCountry IS NOT NULL 
	AND 
	pi.provider.address.alternateCountry.objectStatus = :countryStatus
2

No it’s not possible. Think about it, what should be the column that determines one path is null but the other isn’t? What should the SQL be? One possible interpretation could be:

select
  coalesce(t1.col1, t2.col1),
  coalesce(t1.col2, t2.col2),
...

Another possible interpretation could be:

select
  case when t1.id is null then t2.col1 else t1.col1 end,
  case when t1.id is null then t2.col2 else t1.col2 end,
...

I don’t know if everyone is happy with one or the other solution, but on top of all that, there are further internal challenges with allowing this kind of thing, because functions currently can’t return entities etc.

I’d suggest you to write out the case when statements yourself and construct a DTO from the result.

1 Like