Hello, I have two database tables, one called users and one called user_info. Every user has one record in user_info, so I use "has_one :user_info" in the user model and "belongs_to :users" in the user_info model. What I''d like to do is a find on the user model but be able to specify conditions for fields that exist in the user_info table. For example, I want to find all users named Joe that live in New York City. First names are stored in the users table and city names are stored in the user_info table. So essentially, I want to generate the query: SELECT * FROM users, user_info WHERE users."firstname" = ''Joe'' AND user_info."city" = ''New York City''; What''s the best way to go about this in Rails? Thanks! --~--~---------~--~----~------------~-------~--~----~ 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?hl=en -~----------~----~----~----~------~----~------~--~---
On 8/29/07, Matt White <whiteqt-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > Hello, > > I have two database tables, one called users and one called user_info. > Every user has one record in user_info, so I use "has_one :user_info" > in the user model and "belongs_to :users" in the user_info model. What > I''d like to do is a find on the user model but be able to specify > conditions for fields that exist in the user_info table. > For example, I want to find all users named Joe that live in New York > City. First names are stored in the users table and city names are > stored in the user_info table. So essentially, I want to generate the > query: > SELECT * FROM users, user_info WHERE users."firstname" = ''Joe'' AND > user_info."city" = ''New York City''; > What''s the best way to go about this in Rails? Thanks!You need to use the :include option to tell rails to join the two tables through their associations. Something like the following should work: User.find(:all, :conditions => [''users.first_name = ? and user_info.city = ?'' ''Joe'', ''New York''], :include => ''user_info'') This will join the user_info and the users table using users.id user_info.user_id. Adam --~--~---------~--~----~------------~-------~--~----~ 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?hl=en -~----------~----~----~----~------~----~------~--~---
Hi Matt,> SELECT * FROM users, user_info WHERE users."firstname" = ''Joe'' AND > user_info."city" = ''New York City'';Ever tried this query?> What''s the best way to go about this in Rails? Thanks!# something like this... def find_all_by_firstname_and_city(firstname, city) User.find :all, :joins => ''user_info ON user_infos.user_id = users.id'', :conditions => {''users."firstname"'' => firstname, ''user_infos."city"'' => city} end # or this... def find_all_by_firstname_and_city(firstname, city) User.find :all, :include => :user_info, :conditions => {''users."firstname"'' => firstname, ''user_infos."city"'' => city} end Regards Florian --~--~---------~--~----~------------~-------~--~----~ 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@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Ahh, yes, that does work. I suppose that the table name needs to be
specified in the conditions every time, right? SQL lets you specify an
attribute name without the table name as long as there is no
ambiguity, so my original query could have been:
SELECT * FROM users, user_info WHERE firstname = ''Joe'' AND
city = ''New
York City'';
But I can''t do:
User.find(:all, :conditions => {:first_name => ''Joe'',
:city => ''New
York''}, :include => ''user_info'');
It tries to do users."city" in this case. I don''t suppose
there is any
way to get it done this way?
On Aug 29, 4:22 pm, "Adam Cohen"
<bionicboo...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:> On 8/29/07, Matt White
<whit...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
>
>
> > Hello,
>
> > I have two database tables, one called users and one called user_info.
> > Every user has one record in user_info, so I use "has_one
:user_info"
> > in the user model and "belongs_to :users" in the user_info
model. What
> > I''d like to do is a find on the user model but be able to
specify
> > conditions for fields that exist in the user_info table.
> > For example, I want to find all users named Joe that live in New York
> > City. First names are stored in the users table and city names are
> > stored in the user_info table. So essentially, I want to generate the
> > query:
> > SELECT * FROM users, user_info WHERE users."firstname" =
''Joe'' AND
> > user_info."city" = ''New York City'';
> > What''s the best way to go about this in Rails? Thanks!
>
> You need to use the :include option to tell rails to join the two
> tables through their associations. Something like the following
> should work:
>
> User.find(:all, :conditions => [''users.first_name = ? and
> user_info.city = ?'' ''Joe'', ''New
York''], :include => ''user_info'')
>
> This will join the user_info and the users table using users.id >
user_info.user_id.
>
> Adam
--~--~---------~--~----~------------~-------~--~----~
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?hl=en
-~----------~----~----~----~------~----~------~--~---
Ahh, yes, that does work. I suppose that the table name needs to be specified in the conditions every time, right? SQL lets you specify an attribute name without the table name as long as there is no ambiguity, so my original query could have been: SELECT * FROM users, user_info WHERE firstname = ''Joe'' AND city = ''New York City'' AND users.id = user_info.id; Is there a way to get Rails to perform this query? On Aug 29, 4:32 pm, Florian Aßmann <florian.assm...-htSm2yLGOjU@public.gmane.org> wrote:> Hi Matt, > > > SELECT * FROM users, user_info WHERE users."firstname" = ''Joe'' AND > > user_info."city" = ''New York City''; > > Ever tried this query? > > > What''s the best way to go about this in Rails? Thanks! > > # something like this... > > def find_all_by_firstname_and_city(firstname, city) > User.find :all, > :joins => ''user_info ON user_infos.user_id = users.id'', > :conditions => {''users."firstname"'' => firstname, > ''user_infos."city"'' => city} > end > > # or this... > > def find_all_by_firstname_and_city(firstname, city) > User.find :all, > :include => :user_info, > :conditions => {''users."firstname"'' => firstname, > ''user_infos."city"'' => city} > end > > Regards > Florian--~--~---------~--~----~------------~-------~--~----~ 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@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On 8/29/07, Matt White <whiteqt-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > Is there a way to get Rails to perform this query?did you not see my response? Adam --~--~---------~--~----~------------~-------~--~----~ 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?hl=en -~----------~----~----~----~------~----~------~--~---
Hi Matt,> SELECT * FROM users, user_info WHERE firstname = ''Joe'' AND city = ''New > York City'' AND users.id = user_info.id;I do not really like to put a relation between users.id and user_info.id in Rails, think about renaming user_info to user_infos and user_info.id to user_info.user_id if it''s your foreign key... This way you less noise in your models and UserInfo has its OWN primary_key...> Is there a way to get Rails to perform this query?You can always do User.select_sql(''SELECT users.* FROM users...'')>> def find_all_by_firstname_and_city(firstname, city)should be def self.find_all_by_firstname_and_city(firstname, city) to make it a class method... Regards Florian PS: Adam, I read you post and I think Matt also did - not to intently though... :) --~--~---------~--~----~------------~-------~--~----~ 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?hl=en -~----------~----~----~----~------~----~------~--~---
Matt White wrote:> Hello, > > I have two database tables, one called users and one called user_info. > Every user has one record in user_info, so I use "has_one :user_info" > in the user model and "belongs_to :users" in the user_info model. What > I''d like to do is a find on the user model but be able to specify > conditions for fields that exist in the user_info table. > For example, I want to find all users named Joe that live in New York > City. First names are stored in the users table and city names are > stored in the user_info table. So essentially, I want to generate the > query: > SELECT * FROM users, user_info WHERE users."firstname" = ''Joe'' AND > user_info."city" = ''New York City''; > What''s the best way to go about this in Rails? Thanks!Is there any chance that you''ve really got one model split into two here? In my opinion, if you have a has_one relationship, what you''ve really got is one model split (incorrectly) into two tables. Do you have some reason for this division? If not, the correct thing to do is to merge the two tables. Then, all of your problems of screening users by user_info columns becomes moot. -- 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?hl=en -~----------~----~----~----~------~----~------~--~---
I didn''t get to choose how the tables in the DB are set up, I just have to deal with them the way they are. I agree though - I''m still trying to get them to merge the tables but that''s easier said than done. Adam, of course I read your posts. Did you read mine? You had to specify a table name in the query conditions in the example you gave (aside from the fields where the join takes place). The problem is that in the past the search was performed on fields from one table or the other or both. So if a search is performed for all people named Joe (stored ONLY in the users table) OR a user wants to search for all people living in New York City (stored ONLY in the user_info) table, then to do the find with just one query, there would have to be a join done on the tables so that you can specify field names without having to specify table names. That''s why I was saying in SQL, you don''t need to specify table names on a join query. But from all the examples I''ve seen here, there isn''t a way to do that in Rails unless I specify the SQL myself. On Aug 29, 6:53 pm, Bryan Duxbury <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> Matt White wrote: > > Hello, > > > I have two database tables, one called users and one called user_info. > > Every user has one record in user_info, so I use "has_one :user_info" > > in the user model and "belongs_to :users" in the user_info model. What > > I''d like to do is a find on the user model but be able to specify > > conditions for fields that exist in the user_info table. > > For example, I want to find all users named Joe that live in New York > > City. First names are stored in the users table and city names are > > stored in the user_info table. So essentially, I want to generate the > > query: > > SELECT * FROM users, user_info WHERE users."firstname" = ''Joe'' AND > > user_info."city" = ''New York City''; > > What''s the best way to go about this in Rails? Thanks! > > Is there any chance that you''ve really got one model split into two > here? In my opinion, if you have a has_one relationship, what you''ve > really got is one model split (incorrectly) into two tables. Do you have > some reason for this division? If not, the correct thing to do is to > merge the two tables. Then, all of your problems of screening users by > user_info columns becomes moot. > -- > Posted viahttp://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?hl=en -~----------~----~----~----~------~----~------~--~---
On 8/30/07, Matt White <whiteqt-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > I didn''t get to choose how the tables in the DB are set up, I just > have to deal with them the way they are. I agree though - I''m still > trying to get them to merge the tables but that''s easier said than > done. > > Adam, of course I read your posts. Did you read mine?I saw two posts from you which looked like duplicates, I only read the second one thinking that they were the same message. I wanted to make sure you didn''t miss my post.> You had to > specify a table name in the query conditions in the example you gave > (aside from the fields where the join takes place).I specified a table name, but you don''t have to. I only provided it in case there were ambiguous column names.> The problem is > that in the past the search was performed on fields from one table or > the other or both. So if a search is performed for all people named > Joe (stored ONLY in the users table) OR a user wants to search for all > people living in New York City (stored ONLY in the user_info) table, > then to do the find with just one query, there would have to be a join > done on the tables so that you can specify field names without having > to specify table names. That''s why I was saying in SQL, you don''t need > to specify table names on a join query. But from all the examples I''ve > seen here, there isn''t a way to do that in Rails unless I specify the > SQL myself.try this: user.rb class User < ActiveRecord::Base has_one :info end info.rb class Info < ActiveRecord::Base set_table_name "info" belongs_to :user end script/console User.find(:all, :conditions => [''login = ? and state = ?'', ''johndoe'', ''New York''], :include => :info) login is an attribute of the User class and state is an attribute of Info works for me.. Adam --~--~---------~--~----~------------~-------~--~----~ 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?hl=en -~----------~----~----~----~------~----~------~--~---
Adam, Sorry for the confusion. You are the man. Worked like a charm. Matt On Aug 30, 1:52 pm, "Adam Cohen" <bionicboo...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On 8/30/07, Matt White <whit...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > > > I didn''t get to choose how the tables in the DB are set up, I just > > have to deal with them the way they are. I agree though - I''m still > > trying to get them to merge the tables but that''s easier said than > > done. > > > Adam, of course I read your posts. Did you read mine? > > I saw two posts from you which looked like duplicates, I only read the > second one thinking that they were the same message. I wanted to make > sure you didn''t miss my post. > > > You had to > > specify a table name in the query conditions in the example you gave > > (aside from the fields where the join takes place). > > I specified a table name, but you don''t have to. I only provided it > in case there were ambiguous column names. > > > The problem is > > that in the past the search was performed on fields from one table or > > the other or both. So if a search is performed for all people named > > Joe (stored ONLY in the users table) OR a user wants to search for all > > people living in New York City (stored ONLY in the user_info) table, > > then to do the find with just one query, there would have to be a join > > done on the tables so that you can specify field names without having > > to specify table names. That''s why I was saying in SQL, you don''t need > > to specify table names on a join query. But from all the examples I''ve > > seen here, there isn''t a way to do that in Rails unless I specify the > > SQL myself. > > try this: > > user.rb > class User < ActiveRecord::Base > has_one :info > end > > info.rb > class Info < ActiveRecord::Base > set_table_name "info" > > belongs_to :user > end > > script/console > User.find(:all, :conditions => [''login = ? and state = ?'', ''johndoe'', > ''New York''], :include => :info) > > login is an attribute of the User class and state is an attribute of Info > > works for me.. > > Adam--~--~---------~--~----~------------~-------~--~----~ 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?hl=en -~----------~----~----~----~------~----~------~--~---
Adam, Sorry for the confusion. That worked. You are the man. Now, is there a way to do the same query without knowing what fields you are going to get in advance? It would be nice to just pass in a hash of attributes instead of listing out the conditions. Something like: User.find(:all, :conditions => attributes_hash, :include => :user_info) When I use your method, Adam, I get:>> u = User.find(:all, :conditions => [''firstname = ? and state = ?'', ''Beth'', ''NY''], :include => :user_info)=> [#<User:0x4895504 ... But if I try the way I just suggested, I get:>> attributes = {:firstname => ''Beth'', :state => ''NY''}=> {:firstname=>"Beth", :state=>"NY"}>> u = User.find(:all, :conditions => attributes, :include => :user_info)ActiveRecord::StatementInvalid: RuntimeError: ERROR C42703 Mcolumn users.state does not exist It wouldn''t be too hard to generate the condition string, but a hash would be prettier and easier. Any ideas? Matt On Aug 30, 1:52 pm, "Adam Cohen" <bionicboo...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On 8/30/07, Matt White <whit...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > > > I didn''t get to choose how the tables in the DB are set up, I just > > have to deal with them the way they are. I agree though - I''m still > > trying to get them to merge the tables but that''s easier said than > > done. > > > Adam, of course I read your posts. Did you read mine? > > I saw two posts from you which looked like duplicates, I only read the > second one thinking that they were the same message. I wanted to make > sure you didn''t miss my post. > > > You had to > > specify a table name in the query conditions in the example you gave > > (aside from the fields where the join takes place). > > I specified a table name, but you don''t have to. I only provided it > in case there were ambiguous column names. > > > The problem is > > that in the past the search was performed on fields from one table or > > the other or both. So if a search is performed for all people named > > Joe (stored ONLY in the users table) OR a user wants to search for all > > people living in New York City (stored ONLY in the user_info) table, > > then to do the find with just one query, there would have to be a join > > done on the tables so that you can specify field names without having > > to specify table names. That''s why I was saying in SQL, you don''t need > > to specify table names on a join query. But from all the examples I''ve > > seen here, there isn''t a way to do that in Rails unless I specify the > > SQL myself. > > try this: > > user.rb > class User < ActiveRecord::Base > has_one :info > end > > info.rb > class Info < ActiveRecord::Base > set_table_name "info" > > belongs_to :user > end > > script/console > User.find(:all, :conditions => [''login = ? and state = ?'', ''johndoe'', > ''New York''], :include => :info) > > login is an attribute of the User class and state is an attribute of Info > > works for me.. > > Adam--~--~---------~--~----~------------~-------~--~----~ 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?hl=en -~----------~----~----~----~------~----~------~--~---
On Aug 30, 2007, at 4:26 PM, Matt White wrote:> Adam, > > Sorry for the confusion. That worked. You are the man. Now, is there a > way to do the same query without knowing what fields you are going to > get in advance? It would be nice to just pass in a hash of attributes > instead of listing out the conditions. Something like: > User.find(:all, :conditions => attributes_hash, :include > => :user_info) > > When I use your method, Adam, I get: >>> u = User.find(:all, :conditions => [''firstname = ? and state >>> = ?'', ''Beth'', ''NY''], :include => :user_info) > => [#<User:0x4895504 ... > > But if I try the way I just suggested, I get: >>> attributes = {:firstname => ''Beth'', :state => ''NY''} > => {:firstname=>"Beth", :state=>"NY"} >>> u = User.find(:all, :conditions => attributes, :include >>> => :user_info) > ActiveRecord::StatementInvalid: RuntimeError: ERROR C42703 > Mcolumn users.state does not exist > > It wouldn''t be too hard to generate the condition string, but a hash > would be prettier and easier. Any ideas? > > MattIn general, you need to specify the condition like: :conditions => [ ''users.firstname = ? and info.state = ?'', ''Beth'', ''NY'' ] Because you could have columns with the same name: User.find_by_firstname(''Matt'').update_attribute(:state, ''confusion'') If you have this kind of thing in a controller, you might find it better to push the logic into the model: class User def self.all_with_name_and_state(firstname, state) find(:all, :conditions => [ ''users.firstname = ? and info.state = ?'', firstname, state ], :include => :info) end end This is discussed by Jamis Buck in http://weblog.jamisbuck.org/2006/10/18/skinny-controller-fat-model -Rob> On Aug 30, 1:52 pm, "Adam Cohen" <bionicboo...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: >> On 8/30/07, Matt White <whit...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: >> >> >> >>> I didn''t get to choose how the tables in the DB are set up, I just >>> have to deal with them the way they are. I agree though - I''m still >>> trying to get them to merge the tables but that''s easier said than >>> done. >> >>> Adam, of course I read your posts. Did you read mine? >> >> I saw two posts from you which looked like duplicates, I only read >> the >> second one thinking that they were the same message. I wanted to >> make >> sure you didn''t miss my post. >> >>> You had to >>> specify a table name in the query conditions in the example you gave >>> (aside from the fields where the join takes place). >> >> I specified a table name, but you don''t have to. I only provided it >> in case there were ambiguous column names. >> >>> The problem is >>> that in the past the search was performed on fields from one >>> table or >>> the other or both. So if a search is performed for all people named >>> Joe (stored ONLY in the users table) OR a user wants to search >>> for all >>> people living in New York City (stored ONLY in the user_info) table, >>> then to do the find with just one query, there would have to be a >>> join >>> done on the tables so that you can specify field names without >>> having >>> to specify table names. That''s why I was saying in SQL, you don''t >>> need >>> to specify table names on a join query. But from all the examples >>> I''ve >>> seen here, there isn''t a way to do that in Rails unless I specify >>> the >>> SQL myself. >> >> try this: >> >> user.rb >> class User < ActiveRecord::Base >> has_one :info >> end >> >> info.rb >> class Info < ActiveRecord::Base >> set_table_name "info" >> >> belongs_to :user >> end >> >> script/console >> User.find(:all, :conditions => [''login = ? and state = ?'', ''johndoe'', >> ''New York''], :include => :info) >> >> login is an attribute of the User class and state is an attribute >> of Info >> >> works for me.. >> >> AdamRob Biedenharn http://agileconsultingllc.com Rob-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org --~--~---------~--~----~------------~-------~--~----~ 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?hl=en -~----------~----~----~----~------~----~------~--~---
On 8/30/07, Matt White <whiteqt-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > Adam, > > Sorry for the confusion. That worked. You are the man. Now, is there a > way to do the same query without knowing what fields you are going to > get in advance? It would be nice to just pass in a hash of attributes > instead of listing out the conditions. Something like: > User.find(:all, :conditions => attributes_hash, :include > => :user_info) > > When I use your method, Adam, I get: > >> u = User.find(:all, :conditions => [''firstname = ? and state = ?'', ''Beth'', ''NY''], :include => :user_info) > => [#<User:0x4895504 ... > > But if I try the way I just suggested, I get: > >> attributes = {:firstname => ''Beth'', :state => ''NY''} > => {:firstname=>"Beth", :state=>"NY"} > >> u = User.find(:all, :conditions => attributes, :include => :user_info) > ActiveRecord::StatementInvalid: RuntimeError: ERROR C42703 > Mcolumn users.state does not exist > > It wouldn''t be too hard to generate the condition string, but a hash > would be prettier and easier. Any ideas?looks like when using a hash as your find conditions, rails doesn''t resolve the correct tables, or at least it automatically attempts to find everything through the User model.. Maybe file a bug report about this? I don''t know any solution other than to manually build the query string as you proposed. Adam --~--~---------~--~----~------------~-------~--~----~ 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?hl=en -~----------~----~----~----~------~----~------~--~---
Adam,
Yes, you''re right. Thanks for everyone''s help. For those that
may be
wondering, this method works wonderfully (and feel free to improve on
it if you don''t like it):
def search_customers(contact_info)
user_query = ""
values = []
errors = []
if contact_info.empty?
errors << "Please specify one or more fields."
redirect_to :action => :customer_search
return
end
contact_info.each { |k, v|
user_query << "#{k} = ? AND "
values << v
}
user_query = user_query[/(.+)\sAND\s$/, 1]
@search_results = User.find(
:all,
:conditions => [user_query] + values,
:include => :user_info
)
end
On Aug 30, 3:17 pm, "Adam Cohen"
<bionicboo...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:> On 8/30/07, Matt White
<whit...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
>
>
>
>
> > Adam,
>
> > Sorry for the confusion. That worked. You are the man. Now, is there a
> > way to do the same query without knowing what fields you are going to
> > get in advance? It would be nice to just pass in a hash of attributes
> > instead of listing out the conditions. Something like:
> > User.find(:all, :conditions => attributes_hash, :include
> > => :user_info)
>
> > When I use your method, Adam, I get:
> > >> u = User.find(:all, :conditions => [''firstname =
? and state = ?'', ''Beth'', ''NY''],
:include => :user_info)
> > => [#<User:0x4895504 ...
>
> > But if I try the way I just suggested, I get:
> > >> attributes = {:firstname => ''Beth'',
:state => ''NY''}
> > => {:firstname=>"Beth", :state=>"NY"}
> > >> u = User.find(:all, :conditions => attributes, :include
=> :user_info)
> > ActiveRecord::StatementInvalid: RuntimeError: ERROR C42703
> > Mcolumn users.state does not exist
>
> > It wouldn''t be too hard to generate the condition string, but
a hash
> > would be prettier and easier. Any ideas?
>
> looks like when using a hash as your find conditions, rails
doesn''t
> resolve the correct tables, or at least it automatically attempts to
> find everything through the User model.. Maybe file a bug report about
> this? I don''t know any solution other than to manually build the
> query string as you proposed.
>
> Adam
--~--~---------~--~----~------------~-------~--~----~
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?hl=en
-~----------~----~----~----~------~----~------~--~---
=begin
Hi Matt,
this seems harsh but when I looked at your code I thought: SQL-INJECTION!
next I thought: Where does this Regexp come from?
then I thought: Too much noise in your controller!
finally I thought: Still ambiguous...
Since you''re dealing with legacy tables I''ll stop suggesting
you to change
your table layout.
=end
class UsersController < ApplicationController
# This method searches in the user_info table with the given search params.
#
# params[:search] # => { :state => ''NY'', ... }
#
# Since it uses an instance of hash as search parameter the keys should be
# properly quoted and prefixed with user_info.
#
# After the search is performed it maps the user_info back to their users.
def search_in_userinfo
@users = UserInfo.find(:all,
:include => :user,
:conditions => params[:search]
).map(&:user)
end
# This method search in the user table with the given search params.
#
# params[:search] # => { :firstname => ''Beth'', ... }
#
# Since it uses an instance of hash as search parameter the keys should be
# properly quoted and prefixed with user.
def search_in_user
@users = User.find :all, :include => :user_info,
:conditions => params[:search]
end
# This method searches both tables, the users and the user_info table with
# the given search params.
#
# So, what we need to do is to properly quote and prefix the attributes we
# are searching for.
def search_in_general
# this gives us the relevant column names in the users table...
user_column_names = User.content_columns.map(&:name)
# ...same for user_info table
user_info_column_names = UserInfo.content_columns.map(&:name)
# since a Hash isn''t ordered we''re doing right by getting
the keys into
# an Array
keys = params[:search].keys
# now we are building our query array with properly quoted and prefixed
# column names that''s joined later on
query = keys.map do |key|
# choose table
if user_column_names.include? key
# prefix and quote this key with the User specific methods
''%s.%s = ?'' % [User.table_name,
User.connection.quote(key)]
elsif user_info_column_names.include? key
# prefix and quote this key with the UserInfo specific methods
''%s.%s = ?'' % [UserInfo.table_name,
UserInfo.connection.quote(key)]
else
# drop invalid search attributes...
nil
end
end.compact.join '' AND '' # remove NILs and glue with AND
# Last but not least we perform the search:
@users = User.find :all, :include => :user_info,
:conditions => [query] + params[:search.values_at(keys)]
end
end
=begin
As a last refactoring step I''d put the whole search_in_general method
into a User class method called find_by_params unless it already exist
and the the search_in_general method just do one query into @users...
Regards
Florian
=end
--~--~---------~--~----~------------~-------~--~----~
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?hl=en
-~----------~----~----~----~------~----~------~--~---
Small BuFi: - :conditions => [query] + params[:search.values_at(keys)] + :conditions => [query] + params[:search].values_at(keys) Oyasumi nasai... Florian --~--~---------~--~----~------------~-------~--~----~ 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?hl=en -~----------~----~----~----~------~----~------~--~---