CODEWITHSUNDEEP
elixirecto
script
script

Reputation: 2167

Combine two columns and search on them

I have this query in which we can search based on first name and last name. And it works fine like this:

  |> where(
  [user],
   ilike(user.first_name, ^string) or (user.last_name, ^string) 
 )

It works fine. Now I want to search on full name that is first_name + last_name. I tried this approach of Postgres. If I pass both first name and last name in this query it's returning empty.

 |> where(
  [user],
   ilike(user.first_name, ^string) or ilike(user.last_name, ^string) or ilike(fragment("(?)||' '||(?)", user.first_name, user.last_name), ^string)
 )

But it doesn’t work. it’s returning no result. Is there anything I am missing? Thanks

Upvotes: 3

Views: 738

Answers (2)

Aleksei Matiushkin
Aleksei Matiushkin

Reputation: 121010

The [arguably] more common approach in such a case would be to use Ecto.Query.or_where/3

|> where([user], ilike(user.first_name, ^string))
|> or_where([user], ilike(user.last_name, ^string))
|> or_where([user], ilike(fragment("CONCAT((?), ' ',(?))",
             user.first_name, user.last_name), ^string))

Upvotes: 4

script
script

Reputation: 2167

It seems that

         ilike(fragment(“CONCAT((?), ’ ',(?))”, user.first_name, user.last_name), ^string)

does the trick.

Upvotes: 0

Related Questions