ruby-on-rails - Activerecord 按最佳匹配排序或查询

Question

我有一个带有 first_name 和 last_name 属性的用户模型。我想编写一个 api，可以通过 first_name、last_name 或两者来搜索用户。

问题是当用户同时搜索 first_name 和 last_name 时。我想对结果进行排序，以便完全匹配的记录应该在最上面。与 first_name 和 last_name 匹配的记录应该首先跟有部分匹配的记录。

这是我当前的查询:

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

Answer 1

不是 SQL 方面的专家，所以可能有更好的方法，但要处理这些数据

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" }
])

当我得到(没有排序)

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"]]

然后如果我添加 .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%"]) 我明白了

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"]]

在这些事情上更精通的人可能知道更好的方法，但这看起来已经完成了。