Multi-column search in MySQL

rubyonrails-talk
1

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

2

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.

3

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.

Craig

Best,

4

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.

5

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

6

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