Concat_ws like %value%'

List<InvBook> invBooks = session.createQuery(
"from InvBook i where concat_ws('_', i.name_ru,, case when is null then '_' else i.collection.name_ru end ) like '%qwe%'")

I’ve got two tables inv_book and collection.

InvBook has a embedded field Collection collection binded by
@JoinColumn(name = “collection_id”) within InvBook entity.

This row of code supposed to select all records whether i.collection is null or it isn’t.
(I do need to check if i.collection.name_ru or i.name_ru matching “%qwe%”)

But I’ve got a problem:

this query doesn’t select any InvBooks where collection_id is null. It just ignores them.

Query woks fine, unless i.collection.name_ru being added into it.

For example, select "concat_ws(’_’, i.name_ru,, case when is null then ‘true’ else ‘false’ end ) would work for every single record and return true or false depending on is null or it is not, but if I add i.collection.name_ru then query doesn’t return nothing when collection_id is null.

Try this query instead:

List<InvBook> invBooks = session.createQuery(
"select i " + 
"from InvBook i " + 
"left join i.collection c " + 
"where c is null or " + 
"concat_ws('_', i.name_ru,, c.name_ru) like '%qwe%'")
1 Like

It works! Thank you!
Mhm… can you explain a bit, what was wrong?

Sure. By pushing the check in the WHERE clause, you are have practically issued an INNER JOIN which filters out the parent InvBook rows without an associated child.

1 Like

So simple. thanks again.