如何对firstname、lastname进行sql搜索查询

If i have for example an model:

如果我有一个模型

User :firstname   :lastname
     joseph       smith
     anna         bauer
     ...          ...

And an input field where you can search for an user. The different search queries could be:

还有一个输入字段，你可以在其中搜索用户。不同的搜索查询可以是:

  searchquery1:  smith joseph
  searchquery2:  joseph smith
  searchquery3:  joseph
  searchquery4:  smith

Wich search query in sql would be the best? Actually i could imagine this search query:

sql中的搜索查询是最好的吗?实际上我可以想象这个搜索查询:

  where 'firstname OR lastname  LIKE ?', "%#{search}"

First try:

第一次尝试:

def self.search(search)
 if search
   select('CONCAT(vorname, " ", nachname) as full_name, *')
   where ['vorname LIKE :s OR nachname  LIKE :s OR full_name LIKE :s', :s => "%#{search}"]
 else
  scoped
 end
end

error: SQLite3::SQLException: no such column: full_name

错误:SQLite3::SQLException:没有这样的列:full_name

Second try:

第二次尝试:

def self.search(search)
 if search
  a = search.split
   where('firstname OR lastname  LIKE ?', a[1])
   where('firstname OR lastname  LIKE ?', a[2]) unless a[2].nil?
  else
  scoped
 end
end

Problem: Finds nothing!

问题:发现什么!

4 个解决方案

#1

Yes, you have to search it on both first name and last name like this

是的，你必须搜索它的名字和姓氏，像这样。

select('(firstname || " " || lastname) as \'full_name\', *')
where ['firstname LIKE :s OR lastname  LIKE :s OR full_name LIKE :s', :s => "%#{search}"]

but if the data is too big. You can use the full text search engines like Solr or thinkin sphinx

但如果数据太大。你可以使用像Solr或thinkin sphinx这样的全文搜索引擎

#2

To make search on firstname, lastname and fullname;

搜索姓名、姓氏和全名;

Member.where ['first_name LIKE :s OR last_name LIKE :s OR CONCAT(first_name,last_name) LIKE :s', :s => "#{search.delete(' ')}"]

成员。where ['first_name LIKE:s或last_name:s或CONCAT(first_name,last_name):s'，:s => "#{search.delete(')}

It works!

它的工作原理!

#3

-1

Yes, if you want search on lastname and firstname you must do :

是的，如果你想搜索lastname和firstname，你必须这样做:

User.where('firstname or lastname like ?', params[:search])

An another solution : https://github.com/ernie/squeel, https://github.com/ernie/squeel#predicates

另一个解决方案:https://github.com/ernie/squeel, https://github.com/ernie/squeel#谓词

#4

-1

User.where("(first_name || ' ' || last_name) like :q", :q => params[:search])

#1