Multi-column search in MySQL

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

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.

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,

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.

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

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