Search on multiple columns

Questions / Help
1

II have this quey 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 ilike(user.last_name, ^string) 
     )

It works fine. Now I want to search on a full name that is first_name + last_name. I tried this approach of Postgres.

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

I also tried this:

     ilike(fragment("CONCAT((?), ' ',(?))", user.first_name, user.last_name), ^string)

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

2

seems to be

 ilike(fragment("CONCAT((?), ' ',(?))", user.first_name, user.last_name), ^string)

does the trick

1 Like
3

You could also use || operator, but if you are using PostgreSQL 12+ then the best solution would be to use computed columns. Additionally remember to create functional indices on such columns to speed up such searches.

3 Likes
4

Doing string concatenation to do this seems a little unusual to me. Would the following do what you want?

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

That is, using ‘and’ not ‘or’.

2 Likes