Scala Doobie does not return anything when using OR WHERE

Question

I am trying to select from database with several filters. Code:

// name is List("Denis", "Semen")
val query = name.map(_ + "%").mkString(" OR name LIKE ")  
sql"SELECT * FROM phonebook WHERE name LIKE ${query+"%"}"
   .query[Contact]
   .to[List]
   .transact(tr)

Result query is like this: SELECT * FROM phonebook WHERE name LIKE Denis% OR name LIKE Semen%

And i do not getting anything. I only get something when SELECT is with only one name: SELECT * FROM phonebook WHERE name LIKE Denis%.

I've also tried this:

val query = name.map("'" + _ + "%'").mkString(" OR name LIKE ")  // 'denis%' OR NAME LIKE 'benis%'

Result query it like this: SELECT * FROM phonebook WHERE name LIKE 'Jhon%' OR name LIKE 'Lena%', but in Doobie there is no need to use ', so even one name will not return anything: SELECT * FROM phonebook WHERE name LIKE 'Jhon%'.

Answer 1

The interpolated value (query) is used as paramater value in your select. So when you have multiple names your actual SELECT would look like SELECT * FROM phonebook WHERE name LIKE 'Jhon% OR name LIKE Lena%' (notice where the single quotes are - they include the phrase 'OR LIKE').

Use ++ on Fragment values to build up arbitrary SQL statements. Something like:

val query = name.map(name => fr"name LIKE ${name + "%"}").reduceOption((left, right) => left ++ fr" OR " ++ right).getOrElse(fr"1 = 0")
(sql"SELECT * FROM phonebook WHERE " ++ query)
   .query[Contact]
   .to[List]
   .transact(tr)