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.)