JPA query to concat null field

Question

Hello this is query for concating brand name, title and undertitle

@Query(value="SELECT c FROM ProductOption c WHERE  cast( c.id AS string ) like %:search% or CONCAT(COALESCE(c.product.brand.name,''),' ',c.product.title,' ',c.underTitle) like %:search%")
Page<ProductOption> findAllBySearch(String search,Pageable pageable);

however everytime brand is null, productoption is not included in response..

Basically what i want is to concat brand name , title and undertitle if brand is not null and if it is null concat by title and undertitle..

I have tried many more ways using CASE etd...

Answer 1

When you concatenate the strings in your WHERE clause, you use the path operator "." to navigate from your ProductOption to the product and from there to the brand.

@Query(value="... CONCAT(COALESCE(c.product.brand.name,''),' ',c.product.title,' ',c.underTitle) like %:search%")

This creates 2 INNER JOINs in the generated SQL statement that exclude ProductOptions that don't have a product or a product without a brand.

You can avoid that by defining LEFT JOINs in the FROM clause and referencing the alias of these JOINS in the WHERE clause:

@Query(value="SELECT c FROM ProductOption c LEFT JOIN c.product p LEFT JOIN p.brand b WHERE  cast( c.id AS string ) like %:search% or CONCAT(COALESCE(b.name,''),' ',p.title,' ',c.underTitle) like %:search%")
Page<ProductOption> findAllBySearch(String search,Pageable pageable);