guest
September 18, 2006, 8:41pm
1
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!
guest
September 18, 2006, 8:58pm
2
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
guest
September 18, 2006, 9:09pm
3
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
guest
September 19, 2006, 1:18am
4
Thanks Frederick! That works perfectly!
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
guest
September 18, 2006, 10:35pm
5
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