Concat Two fields in find

Wow, this is ever strange:

If I do a find on a model with the following conditions:

:conditions => [“first_name = :query OR last_name = :query OR
(first_name + ’ ’ + last_name) = :query”, {:query => @query}])

It brings back all the records if I search for like John D. (wich is
first_name + last_name). Digging a little deeper, it seems that this is
not Rails, but MySQL bringing back all the records for that search.

Does anyone know how to combine two fields in a MySQL query? This works
fine in SQL Server, but I can’t seem to figure it out for MySQL.

Thanks in advance!

Correct me if I’m wrong, but I do believe that you’ll need to do such
field
concatenation in the SELECT section of the clause, like so:

SELECT first_name, last_name, first_name + ’ ’ + last_name AS full_name
FROM
table_name
WHERE first_name = ? OR last_name = ? OR full_name = ?

Jason

Hey Jason,

Thanks for the response. That doesn’t seem to work either. Here’s what I
get when I just try to run the Query in MySQL

Error Code : 1054
Unknown column ‘full_name’ in ‘where clause’
(0 ms taken)

And that’s with the field alias in the SELECT. Odd.

Jason R. wrote:

Correct me if I’m wrong, but I do believe that you’ll need to do such
field
concatenation in the SELECT section of the clause, like so:

SELECT first_name, last_name, first_name + ’ ’ + last_name AS full_name
FROM
table_name
WHERE first_name = ? OR last_name = ? OR full_name = ?

Jason

Thanks Frederick! That works perfectly!

:slight_smile:

Frederick C. wrote:

Michael L. wrote:

Hey Jason,

Thanks for the response. That doesn’t seem to work either. Here’s what I
get when I just try to run the Query in MySQL

Error Code : 1054
Unknown column ‘full_name’ in ‘where clause’
(0 ms taken)

And that’s with the field alias in the SELECT. Odd.

This is expected (see
http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html).
In addition in mysql the ‘+’ doesn’t do what you think it does. You want
to use concat(first_name, ’ ', last_name) instead.

Fred

Michael L. wrote:

Hey Jason,

Thanks for the response. That doesn’t seem to work either. Here’s what I
get when I just try to run the Query in MySQL

Error Code : 1054
Unknown column ‘full_name’ in ‘where clause’
(0 ms taken)

And that’s with the field alias in the SELECT. Odd.

This is expected (see
http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html).
In addition in mysql the ‘+’ doesn’t do what you think it does. You want
to use concat(first_name, ’ ', last_name) instead.

Fred