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 Doe (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!
--
Posted via http://www.ruby-forum.com/.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---
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 On 9/18/06, Guest <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> > > 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 Doe (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! > > -- > Posted via http://www.ruby-forum.com/. > > > >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---
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 Roelofs 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-- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---
Michael Leung 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 -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---
Thanks Frederick! That works perfectly! :) Frederick Cheung wrote:> Michael Leung 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-- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---