Concat_ws like %value%'

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

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, i.id, case when i.collection.id is null then ‘true’ else ‘false’ end ) would work for every single record and return true or false depending on i.collection.id 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.

2

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, i.id, c.name_ru) like '%qwe%'")
.list();
1 Like
3

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

4

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
5

So simple. thanks again.