Rails - Dec 2010 - 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.

-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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,
-- 
Marnen Laibow-Koser
http://www.marnen.org
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org

Sent from my iPhone

-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On 7 December 2010 13:38, Srikanth Jeeva
<lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>
wrote:
> 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
>
> --
> Posted via http://www.ruby-forum.com/.
>
> --
> You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
> To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
> To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
> For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
>
>
-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Colin Law wrote in post #966865:
> On 7 December 2010 13:38, Srikanth Jeeva
<lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote:
>> 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,
-- 
Marnen Laibow-Koser
http://www.marnen.org
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org

Sent from my iPhone

-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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

-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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,
-- 
Marnen Laibow-Koser
http://www.marnen.org
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org

Sent from my iPhone

-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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

-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On 7 December 2010 14:04, Srikanth Jeeva
<lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>
wrote:
>>> 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]%''])
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

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On 7 December 2010 15:37, Colin Law
<clanlaw-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org>
wrote:
> On 7 December 2010 14:04, Srikanth Jeeva
<lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote:
>>>> keyword = "Srikanth Jeeva"
>>
>> 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.)

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.