Spring JPA @Query where clause with 'like' and 'or'

Question

I have the following query:

@Query("select c from Category c where ( (lower(c.name) like '%' || lower(:searchText) || '%') or (lower(c.description) like '%' || lower(:searchText)) || '%')")

My product is designed to run in multiple platform, I am getting an error on postgreSQL which is:

PSQLException: ERROR: argument of OR must be type boolean, not type text.

Which is undestandable since the like clause return strings. But I wasn't able to perform the search in one query request. So the question is how can I perform a search where the where conditions refer to 2 differnt columns and use the 'like' operator.

Try adding explicit parentheses to group operations. I think you're having operator precedence issues. — Craig Ringer, May 20 '13 at 08:13
You mean to add parentheses around the comparison paramenter?, if so, i didn't work — Fabricio Z, May 20 '13 at 09:04
can you *show me* what you tried (Where you added the parens) and exactly it "didn't work"? Please include your PostgreSQL version and the real underlying SQL that EclipesLink/Hibernate/whatever turned that HQL into. You can get that from the PostgreSQL logs with `log_statement = 'all'` or from your ORM's logs. — Craig Ringer, May 20 '13 at 11:21

score 3 · Accepted Answer · answered May 20 '13 at 10:10

The parentheses you have are not correct the following should work:

@Query("select c from Category c " +
       "where (lower(c.name) like ('%' || lower(:searchText) || '%')) " +
         " or (lower(c.description) like ('%' || lower(:searchText) || '%'))")

Spring JPA @Query where clause with 'like' and 'or'

1 Answers1