What is the proper syntax for an Ecto query using ilike and SQL concatenation?

I am attempting to make an Ecto query where I concatenate the first and last name of a given query result, then perform an ilike search using a query string. For example, I may want to search the database for all names that start with "Bob J". Currently, my code looks like this:

pending_result_custom_search = from result in pending_result_query,
         where: ilike(fragment("CONCAT(?, '',?)", result.first_name, result.last_name), ^query)

(pending_result_query is a previous query that I am composing on top of)

This approach does not work and I continue to get an empty query set. If I perform the query doing something like this

query = "Bob"
pending_result_custom_search = from result in pending_result_query,
         where: ilike(fragment("CONCAT(?, '',?)", "%Bob%", ""), ^query)

I get the correct functionality.

What is the proper syntax to get the first approach working properly?

Solution

I think in your case I would use only fragment, e.g.

query = "%" <> "Bob" <> "%"
pending_result_custom_search = from result in pending_result_query,
         where: fragment("first_name || last_name ILIKE ?", ^query)

That way you can shift the focus to PostGres and use its functions instead of worrying too much about the Ecto abstractions of them. In the above example, I used || to concatenate column values, but you could use PostGres' CONCAT() if you desired:

pending_result_custom_search = from result in pending_result_query,
         where: fragment("CONCAT(first_name, last_name) ILIKE ?", ^query)

Note that both examples here did not include a space between first_name and last_name. Also, I added the % characters to the search query before binding it.