John Smith John Smith - 6 months ago 22
SQL Question

How to make sql search query for firstname , lastname

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:

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


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!

Answer

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

Comments