I could handle this fairly easily in PostgreSQL but for this project, I
am using MySQL...
Postgres,
@members = Member.find(:all,
:conditions => ["LOWER(first_name||last_name) LIKE ?",
''%'' + params[:member][:name].downcase +
''%''])
MySQL ?
@members = Member.find(:all,
:conditions => ["first_name LIKE ? OR last_name LIKE ?",
"%" + params[:member][:name].downcase + "%",
"%" + params[:member][:name].downcase + "%"])
Apparently MySQL is case insensitive search by default which is fine.
But the only thing I can figure out is that I have to search each field
separately which seems rather crazy to me. I don''t know that this
matters but this is for auto_complete type function.
Anyone have a clue stick for me?
Craig
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
--
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.
Craig White wrote:> I don''t know that this > matters but this is for auto_complete type function. > > Anyone have a clue stick for me? >Don''t reinvent the wheel! There are many auto-complete plugins out there already. One of them may do precisely what you need. -- 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.
Craig White wrote:> I could handle this fairly easily in PostgreSQL but for this project, I > am using MySQL... > > Postgres, > > @members = Member.find(:all, > :conditions => ["LOWER(first_name||last_name) LIKE ?", > ''%'' + params[:member][:name].downcase + ''%'']) > > MySQL ? > > @members = Member.find(:all, > :conditions => ["first_name LIKE ? OR last_name LIKE ?", > "%" + params[:member][:name].downcase + "%", > "%" + params[:member][:name].downcase + "%"]) > > Apparently MySQL is case insensitive search by default which is fine. > But the only thing I can figure out is that I have to search each field > separately which seems rather crazy to me.MySQL departs from the SQL standard by using concat() instead of ||, or you can turn on ANSI_mode to use ||.> I don''t know that this > matters but this is for auto_complete type function. > > Anyone have a clue stick for me? >I agree with Aldric: don''t reinvent the wheel.> CraigBest, -- Marnen Laibow-Koser http://www.marnen.org marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- Posted via http://www.ruby-forum.com/. --00032555412abb81f1047c827cd9 Content-Type: text/plain; charset=ISO-8859-1 -- 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. --00032555412abb81f1047c827cd9--
On Wed, 2010-01-06 at 18:43 +0100, Marnen Laibow-Koser wrote:> Craig White wrote: > > I could handle this fairly easily in PostgreSQL but for this project, I > > am using MySQL... > > > > Postgres, > > > > @members = Member.find(:all, > > :conditions => ["LOWER(first_name||last_name) LIKE ?", > > ''%'' + params[:member][:name].downcase + ''%'']) > > > > MySQL ? > > > > @members = Member.find(:all, > > :conditions => ["first_name LIKE ? OR last_name LIKE ?", > > "%" + params[:member][:name].downcase + "%", > > "%" + params[:member][:name].downcase + "%"]) > > > > Apparently MySQL is case insensitive search by default which is fine. > > But the only thing I can figure out is that I have to search each field > > separately which seems rather crazy to me. > > MySQL departs from the SQL standard by using concat() instead of ||, or > you can turn on ANSI_mode to use ||. > > > I don''t know that this > > matters but this is for auto_complete type function. > > > > Anyone have a clue stick for me? > > > > I agree with Aldric: don''t reinvent the wheel.---- I am using the plugin but I want to search for the string in either the first_name or last_name columns. It appears that the CONCAT function is not useful for me in this case...>> Member.find(:all, :conditions => ["CONCAT(first_name, last_name)LIKE ?", "cra"]) => [] and should have returned a single result. Craig -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- 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 06/01/2010 6:36 PM, Craig White wrote:> On Wed, 2010-01-06 at 18:43 +0100, Marnen Laibow-Koser wrote: > >> Craig White wrote: >> >>> I could handle this fairly easily in PostgreSQL but for this project, I >>> am using MySQL... >>> >>> Postgres, >>> >>> @members = Member.find(:all, >>> :conditions => ["LOWER(first_name||last_name) LIKE ?", >>> ''%'' + params[:member][:name].downcase + ''%'']) >>> >>> MySQL ? >>> >>> @members = Member.find(:all, >>> :conditions => ["first_name LIKE ? OR last_name LIKE ?", >>> "%" + params[:member][:name].downcase + "%", >>> "%" + params[:member][:name].downcase + "%"]) >>> >>> Apparently MySQL is case insensitive search by default which is fine. >>> But the only thing I can figure out is that I have to search each field >>> separately which seems rather crazy to me. >>> >@members = Member.find(:all, :conditions => ["LOWER(CONCAT(first_name,last_name)) LIKE ?", ''%'' + params[:member][:name].downcase + ''%'']) This will behave identically to your postgres example as you are adding the two fields together and then comparing with the search string as a single field. Cheers, Gary. -- 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 Wed, 2010-01-06 at 18:59 +0000, Gary Doades wrote:> On 06/01/2010 6:36 PM, Craig White wrote: > > On Wed, 2010-01-06 at 18:43 +0100, Marnen Laibow-Koser wrote: > > > >> Craig White wrote: > >> > >>> I could handle this fairly easily in PostgreSQL but for this project, I > >>> am using MySQL... > >>> > >>> Postgres, > >>> > >>> @members = Member.find(:all, > >>> :conditions => ["LOWER(first_name||last_name) LIKE ?", > >>> ''%'' + params[:member][:name].downcase + ''%'']) > >>> > >>> MySQL ? > >>> > >>> @members = Member.find(:all, > >>> :conditions => ["first_name LIKE ? OR last_name LIKE ?", > >>> "%" + params[:member][:name].downcase + "%", > >>> "%" + params[:member][:name].downcase + "%"]) > >>> > >>> Apparently MySQL is case insensitive search by default which is fine. > >>> But the only thing I can figure out is that I have to search each field > >>> separately which seems rather crazy to me. > >>> > > > > @members = Member.find(:all, > :conditions => ["LOWER(CONCAT(first_name,last_name)) LIKE ?", > ''%'' + params[:member][:name].downcase + ''%'']) > > > This will behave identically to your postgres example as you are adding the two fields together and then comparing with the search string as a single field.---- indeed - perfect - thanks Craig -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- 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.