Rails: Efficiently searching by both firstname and surname

I'm trying to create a 'search box' that matches users by name.

The difficulty is that a user has both a firstname and a surname. Each of those can have spaces in them (eg "Jon / Bon Jovi", or "Neil Patrick / Harris"), and I'm wondering about the most efficient way to ensure the search is carried out on a concatenation of both the firstname and surname fields.

The list of users is quite large, so performance is a concern. I could just throw a "fullname" def in the user model, but I suspect this isn't the wisest move performance wise. My knowledge of multi-column rails indexes is weak, but I susp开发者_开发问答ect there's a way of doing it via an index with a " " in it?

Just to clarify, I don't need fuzzy matching - exact match only is fine...I just need it to be run on a concatenation of two fields.

Cheers...

---

You could create a new field in your database called full_name with a regular index, then use a callback to populate this whenever the record is saved/updated:

before_save :populate_full_name

protected

def populate_full_name
  self.full_name = "#{first_name} #{last_name}"
end

---

If you can modify the database, you can and should use the solution provided by gjb.

Here is the solution that does not require you to alter the database. Simply gather all the possible first-name/last-name pairs you can get from the search box. Some code:

# this method returns an array of first/last name pairs given the string
# it returns nil when the string does not look like a proper name
# (i.e. "Foo Bar" or "Foo Bar Baz", but not "Foo" or "Foo "
def name_pairs(string)
  return nil unless string =~ /^\w+(\s+\w+)+$/
  words = string.split(/\s+/)  # split on spaces
  result = []
  # in the line below: note that there is ... and .. in the ranges
  1.upto(words.size-1) {|n| result << [words[0...n], words[n..-1]]}
  result.collect {|f| f.collect {|nm| nm.join(" ")}}
end

This method gives you an array of two-element arrays, which you can use to create an or query. Here is how the method looks:

#> name_pairs("Jon Bon Jovi")
=> [["Jon", "Bon Jovi"], ["Jon", "Bon Jovi"]]
#> name_pairs("John Bongiovi")
=> [["John", "Bongiovi"]]
#> name_pairs("jonbonjovi")
=> nil

Of course, this method is not perfect (it does not capitalise the names, but you can do it after splitting) and is probably not optimal in terms of speed, but it works. You can also reopen String and add the method there, so you can go with "Jon Bon Jovi".name_pairs.