Search Fullname, in two fields firstname, lastname

Rails
1

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 J.”

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 J.”

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

2

Srikanth J. 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 J.”

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,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

Sent from my iPhone

3

On 7 December 2010 13:38, Srikanth J. [email protected] wrote:

keyword = “Srikanth J.”
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

4

Colin L. wrote in post #966865:

On 7 December 2010 13:38, Srikanth J. [email protected] wrote:

keyword = “Srikanth J.”
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,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

Sent from my iPhone

5

keyword = “Srikanth J.”
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 J.”
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.

6

Srikanth J. wrote in post #966869:

keyword = “Srikanth J.”
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 J.”
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,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

Sent from my iPhone

7

On 7 December 2010 14:04, Srikanth J. [email protected] wrote:

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]%’])

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

8

On 7 December 2010 15:37, Colin L. [email protected] wrote:

On 7 December 2010 14:04, Srikanth J. [email protected] wrote:

keyword = “Srikanth J.”

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

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.

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

9

Srikanth J. 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