Multi-column search in MySQL

Rails
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 W. 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 W. 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,
–Â
Marnen Laibow-Koser
http://www.marnen.org
[email protected]

4

On Wed, 2010-01-06 at 18:43 +0100, Marnen Laibow-Koser wrote:

MySQL ?
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

5

On 06/01/2010 6:36 PM, Craig W. wrote:

 :conditions =>  ["LOWER(first_name||last_name) LIKE ?",

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.

6

On Wed, 2010-01-06 at 18:59 +0000, Gary D. wrote:

 @members = Member.find(:all,

Apparently MySQL is case insensitive search by default which is fine.
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