Search Fullname, in two fields firstname, lastname.

Hi,

I have a Model named User.

It has two columns firstname, lastname.

There a record with id=1, firstname="srikanth" and lastname="Jeeva"

While Using search:

keyword = "Srikanth Jeeva"

And I write a Query:

User.find(:all, :conditions=>["firstname like ? or lastname like ?",
'%keyword%', '%keyword%'])

Ofcourse this will give 0 result, as firstname is "srikanth" and i'm
searching for "Srikanth Jeeva"

How can i write query for firstname and lastname, so that I can get the
record.

Srikanth Jeeva wrote in post #966858:
[...]

User.find(:all, :conditions=>["firstname like ? or lastname like ?",
'%keyword%', '%keyword%'])

Ofcourse this will give 0 result, as firstname is "srikanth" and i'm
searching for "Srikanth Jeeva"

How can i write query for firstname and lastname, so that I can get the
record.

Check your DB's documentation for how to do case-insensitive searches.
Usually there's a keyword such as "ilike", or you may need to transform
the case on the DB side before doing the comparison.

Best,

Hi,

I have a Model named User.

It has two columns firstname, lastname.

There a record with id=1, firstname="srikanth" and lastname="Jeeva"

While Using search:

keyword = "Srikanth Jeeva"

And I write a Query:

User.find(:all, :conditions=>["firstname like ? or lastname like ?",
'%keyword%', '%keyword%'])

Ofcourse this will give 0 result, as firstname is "srikanth" and i'm
searching for "Srikanth Jeeva"

How can i write query for firstname and lastname, so that I can get the
record.

Split the keyword into parts and use the two parts separately in the
query. Have a look at the split method of ruby String class.

Colin

Colin Law wrote in post #966865:

keyword = "Srikanth Jeeva"
record.

Split the keyword into parts and use the two parts separately in the
query. Have a look at the split method of ruby String class.

Quite right. I misunderstood the question.

Colin

Best,

keyword = "Srikanth Jeeva"
record.

Split the keyword into parts and use the two parts separately in the
query. Have a look at the split method of ruby String class.

Thanks for response.
After splitting i write query like this,

keyword = "Srikanth Jeeva"
splitted_word = keyword.split(" ")

User.find(:all, :conditions=>["firstname like ? or lastname like ? or
firstname like ? or lastname like ?", '%splitted_word[0]%',
'%splitted_word[0]%', '%splitted_word[1]%', '%splitted_word[1]%'])

Is there any better way of doing this?

Thanks.

Srikanth Jeeva wrote in post #966869:

keyword = "Srikanth Jeeva"
record.

Split the keyword into parts and use the two parts separately in the
query. Have a look at the split method of ruby String class.

Thanks for response.
After splitting i write query like this,

keyword = "Srikanth Jeeva"
splitted_word = keyword.split(" ")

User.find(:all, :conditions=>["firstname like ? or lastname like ? or
firstname like ? or lastname like ?", '%splitted_word[0]%',
'%splitted_word[0]%', '%splitted_word[1]%', '%splitted_word[1]%'])

Is there any better way of doing this?

Yes. Use named placeholders in the query string so you don't have to
repeat yourself.

Thanks.

Best,

Srikanth Jeeva wrote in post #966869:

User.find(:all, :conditions=>["firstname like ? or lastname like ? or
firstname like ? or lastname like ?", '%splitted_word[0]%',
'%splitted_word[0]%', '%splitted_word[1]%', '%splitted_word[1]%'])

Is there any better way of doing this?

Another option, depending on your database and your particular needs, is
to use full-text search.

http://dev.mysql.com/doc/refman/5.0/en/fulltext-natural-language.html

Don't forget to include checks to make sure you have two words and
adjust the query accordingly, unless you know exactly the format of
keyword of course.

Colin

yes... what if the user-supplied search word is only one name, or three names?

The solution to the problem that I use is this snippet (where "value"
is a user-entered search value, and the following chunk of code is
just part of a larger search/sort/order query, but all the important
parts are here):

  conditions_sql = []
  conditions_values = []
  value.split(" ").flatten.uniq.each do |name_part|
    conditions_sql << " (people.firstname LIKE ? OR people.lastname
LIKE ? OR people.othernames LIKE ?)"
    3.times {conditions_values << "%#{name_part}%"}
  end
  conditions_values.unshift conditions_sql.join(" AND ")
  Person.find(:conditions => conditions_values)

(And after looking at it for the first time in ages myself; as Marnen
points out, I could probably replace the "3.times do ..." bit with
named placeholders to streamline it a little more.)