Rails search users by first_name inside full_name

Question

I have User model with attribute full_name. And I want users to search just by their first or last name. I have tried

User.where("username= :username OR full_name LIKE :full_name", username: "rony", full_name: "rony")

But the problem is when I search with this only query, full_name will always return nill because this query only search exact "rony" in full_name instead of for example "rony sianturi", do you have any other suggestion in doing this query. Thank you.

I have search similar questions, but all other questions is the invese of this question. So no luck

Thank you.

Moamen Naanou · Accepted Answer · 2018-03-05T06:45:00.697

1

Try to use like with wildcard:

User.where("username = ? OR full_name like ?", "rony", "%rony%")

edited Mar 05 '18 at 06:45

answered Mar 05 '18 at 05:59

Moamen Naanou

1,683
1
21
45

error `ActiveRecord::StatementInvalid: SQLite3::SQLException: near "ilike": syntax error: SELECT "users".* FROM "users" WHERE (username = 'rony' OR full_name ilike '%rony%') LIMIT ?` – rony Mar 05 '18 at 06:42
@chronycles you are trying to retrieve records from sqlite not postgres, check my updated answer but this is case sensitive. If you need to check `full_name` with case insensitive, check this https://stackoverflow.com/questions/15480319/case-sensitive-and-insensitive-like-in-sqlite – Moamen Naanou Mar 05 '18 at 06:45
I got this error since 'rony' string is inside a variable, how to remove the character (" ")? `ActiveRecord::StatementInvalid: SQLite3::SQLException: near "%": syntax error: SELECT "users".* FROM "users" WHERE (username= 'rony' OR full_name= %'rony'%) LIMIT ?` – rony Mar 05 '18 at 07:17
no worries, it works now. just a matter of syntax. Thank you sir – rony Mar 05 '18 at 07:25

score 0 · Answer 2 · answered Mar 05 '18 at 06:44

You can build the query with Arel. For example the search query here would be like

User.where(
  User.arel_table[:name].eq("#{key_word}").
    or(
      User.arel_table[:name].lower.matches("%#{key_word.downcase}%")
    )
)

Please check it out Arel documentation for more details & advanced queries.

Rails search users by first_name inside full_name

2 Answers2