CODEWITHSUNDEEP
ruby-on-railspostgresqlruby-on-rails-4activerecord
shriidhar
shriidhar

Reputation: 427

Activerecord sort OR query by best match

I have a User model with first_name and last_name attributes. I want to write an api where a user can be searched either by first_name, last_name, or by both.

The problem is when a user searches by both first_name and last_name. I want to sort the result so that the fully matched record should be on top. A record matching both first_name and last_name should be first followed by the records with partial match.

Here is my current query:

q.where('first_name like ? or last_name like ?', "%#{first_name}%", "%#{last_name}%")

Upvotes: 0

Views: 421

Answers (1)

Simple Lime
Simple Lime

Reputation: 11090

Not an expert in SQL so there might be a better way, but playing around with this data

User.create([
  { first_name: "Random", last_name: "Last" },
  { first_name: "Random", last_name: "Random" },
  { first_name: "First", last_name: "Random" },
  { first_name: "First", last_name: "Last" },
  { first_name: "Not First", last_name: "Last" },
  { first_name: "First", last_name: "Not Last" },
  { first_name: "Not First", last_name: "Not Last" }
])

When I get (without sorting)

User.where("first_name like ? or last_name like ?", "%First%", "%Last%").collect { |u| [u.first_name, u.last_name] }
# User Load (0.2ms)  SELECT "users".* FROM "users" WHERE (first_name like '%First%' or last_name like '%Last%')
=> [["Random", "Last"], ["First", "Random"], ["First", "Last"], ["Not First", "Last"], ["First", "Not Last"], ["Not First", "Not Last"]]

and then if I add on .order(User.send(:sanitize_sql, ["first_name like ? and last_name like ? DESC, first_name like ? OR last_name like ? DESC", "%First%", "%Last%", "%First%", "%Last%"]) I get

User.where("first_name like ? or last_name like ?", "%First%", "%Last%").order(User.send(:sanitize_sql, ["first_name like ? and last_name like ? DESC, first_name like ? OR last_name like ? DESC", "%First%", "%Last%", "%First%", "%Last%"])).collect { |u| [u.first_name, u.last_name] } 
# User Load (0.2ms)  SELECT "users".* FROM "users" WHERE (first_name like '%First%' or last_name like '%Last%') ORDER BY first_name like '%First%' and last_name like '%Last%' DESC, first_name like '%First%' OR last_name like '%Last%' DESC
=> [["First", "Last"], ["Not First", "Last"], ["First", "Not Last"], ["Not First", "Not Last"], ["Random", "Last"], ["First", "Random"]]

Someone who's more of an expert in these things might know of a better way, but this looks like it's getting it done.

Upvotes: 2

Related Questions