Hi, I have two tables, "users" and "users_emails". users have the following columns: |id|name| users_emails have the following columns: |user_id|email| The classes look like the following: class UsersEmail< ActiveRecord::Base belongs_to :user end class User< ActiveRecord::Base has_many :users_emails end --- What is the best way (the rails way using ActiveRecord methods) to find a user based on "User.name" and "UsersEmail.email". I want the User who has a particular name and particular email. I can do something like: User.find(:all, :condition["name = :name", ''joe''], :join => ["do a join on Users and UsersEmail"]) where I specify the user.name in the :condition, but I was wondering if there is a better way of doing this (without explicitly witting out the join)? Any help would be greatly appreciated. -- 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 -~----------~----~----~----~------~----~------~--~---
Frederick Cheung
2008-Aug-23 19:27 UTC
Re: Question about ActiveRecord assoication and :join
On 23 Aug 2008, at 19:58, Mike Jowee wrote:> > Hi, > I have two tables, "users" and "users_emails". > > users have the following columns: |id|name| > users_emails have the following columns: |user_id|email| > > The classes look like the following: > class UsersEmail< ActiveRecord::Base > belongs_to :user > end > > class User< ActiveRecord::Base > has_many :users_emails > end > > --- > > What is the best way (the rails way using ActiveRecord methods) to > find > a user based on "User.name" and "UsersEmail.email". I want the User > who > has a particular name and particular email. > > I can do something like: > User.find(:all, :condition["name = :name", ''joe''], > :join => ["do a join on Users and UsersEmail"]) > where I specify the user.name in the :condition, but I was wondering > if > there is a better way of doing this (without explicitly witting out > the > join)?If you do :joins => :users_emails ActiveRecord will write the join clause for you Fred> > > Any help would be greatly appreciated. > -- > 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 -~----------~----~----~----~------~----~------~--~---
hm..thanks. But I was wondering if there was a way to avoid the :join condition all together in the :find. Such as using ":though" in the has_many method in ActiveRecord? Frederick Cheung wrote:> On 23 Aug 2008, at 19:58, Mike Jowee wrote: > >> end >> who >> has a particular name and particular email. >> >> I can do something like: >> User.find(:all, :condition["name = :name", ''joe''], >> :join => ["do a join on Users and UsersEmail"]) >> where I specify the user.name in the :condition, but I was wondering >> if >> there is a better way of doing this (without explicitly witting out >> the >> join)? > > If you do :joins => :users_emails ActiveRecord will write the join > clause for you > > 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?hl=en -~----------~----~----~----~------~----~------~--~---
Frederick Cheung
2008-Aug-23 20:00 UTC
Re: Question about ActiveRecord assoication and :join
On 23 Aug 2008, at 20:56, Mike Jowee wrote:> > hm..thanks. > > But I was wondering if there was a way to avoid the :join condition > all > together in the :find. Such as using ":though" in the has_many method > in ActiveRecord?through is for something different (when you have a many to many association) which you don''t have here. I''m not sure what your aversion to :joins is when what you''re doing is inherently a joiny sort of thing. Fred> > > > Frederick Cheung wrote: >> On 23 Aug 2008, at 19:58, Mike Jowee wrote: >> >>> end >>> who >>> has a particular name and particular email. >>> >>> I can do something like: >>> User.find(:all, :condition["name = :name", ''joe''], >>> :join => ["do a join on Users and UsersEmail"]) >>> where I specify the user.name in the :condition, but I was wondering >>> if >>> there is a better way of doing this (without explicitly witting out >>> the >>> join)? >> >> If you do :joins => :users_emails ActiveRecord will write the join >> clause for you >> >> 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?hl=en -~----------~----~----~----~------~----~------~--~---
I tried the following, but it did not work. I think I''mmissing something: User.find(:all, :conditions => ["name = ''joe'' and email = ''foo-+LBmYUDmh58@public.gmane.org"], :joins => :users_emails] It didn''t seem to generate the join properly. Any help would be greatly appreciated. -- 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 -~----------~----~----~----~------~----~------~--~---
Mike - On 23-Aug-08, at 10:29 PM, Mike Jowee wrote:> > I tried the following, but it did not work. I think I''mmissing > something: > > User.find(:all, :conditions => ["name = ''joe'' and email > ''foo-+LBmYUDmh58@public.gmane.org"], :joins => :users_emails] > > It didn''t seem to generate the join properly. > > Any help would be greatly appreciated.the :joins key takes either a string or the name of the association (like the :include param). So take a look at your User model, where it references UserEmail It likely reads something like: has_many :user_emails if that''s the case then the join will read ":join => :user_emails". Jodi --~--~---------~--~----~------------~-------~--~----~ 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, I do have the ":joins => :users_emails" User.find(:all, :conditions => ["name = ''joe'' and email ''foo-+LBmYUDmh58@public.gmane.org"], :joins => :users_emails] But when I look at the SQL being generated, it looks like a join isn''t taking place. The actual error I get is that it cannot find the attribute ''email''. Any help would be greatly appreciated. Jodi Showers wrote:> Mike - > > On 23-Aug-08, at 10:29 PM, Mike Jowee wrote: > >> >> I tried the following, but it did not work. I think I''mmissing >> something: >> >> User.find(:all, :conditions => ["name = ''joe'' and email >> ''foo-+LBmYUDmh58@public.gmane.org"], :joins => :users_emails] >> >> It didn''t seem to generate the join properly. >> >> Any help would be greatly appreciated. > > > the :joins key takes either a string or the name of the association > (like the :include param). > > So take a look at your User model, where it references UserEmail > > It likely reads something like: > > has_many :user_emails > > if that''s the case then the join will read ":join => :user_emails". > > Jodi-- 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 -~----------~----~----~----~------~----~------~--~---
MIke - if you re-read my note, you''ll see I suggested "user_emails". (user is singular) What is the name of your association ? On 23-Aug-08, at 10:49 PM, Mike Jowee wrote:> > Hi, > I do have the ":joins => :users_emails" > > User.find(:all, :conditions => ["name = ''joe'' and email > ''foo-+LBmYUDmh58@public.gmane.org"], :joins => :users_emails] > > But when I look at the SQL being generated, it looks like a join isn''t > taking place. The actual error I get is that it cannot find the > attribute ''email''. > > Any help would be greatly appreciated. > > > > Jodi Showers wrote: >> Mike - >> >> On 23-Aug-08, at 10:29 PM, Mike Jowee wrote: >> >>> >>> I tried the following, but it did not work. I think I''mmissing >>> something: >>> >>> User.find(:all, :conditions => ["name = ''joe'' and email >>> ''foo-+LBmYUDmh58@public.gmane.org"], :joins => :users_emails] >>> >>> It didn''t seem to generate the join properly. >>> >>> Any help would be greatly appreciated. >> >> >> the :joins key takes either a string or the name of the association >> (like the :include param). >> >> So take a look at your User model, where it references UserEmail >> >> It likely reads something like: >> >> has_many :user_emails >> >> if that''s the case then the join will read ":join => :user_emails". >> >> Jodi > > -- > 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 -~----------~----~----~----~------~----~------~--~---
Jodi Showers wrote:> MIke - if you re-read my note, you''ll see I suggested "user_emails". > (user is singular) > > What is the name of your association ?I put the Model classes in my original post: The classes look like the following: class UsersEmail< ActiveRecord::Base belongs_to :user end class User< ActiveRecord::Base has_many :users_emails end -- 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 -~----------~----~----~----~------~----~------~--~---
Mike, What does: UsersEmail.find(:all, :conditions => ["email = ''joe-hcDgGtZH8xNBDgjK7y7TUQ@public.gmane.org'' "]) return? (Just do a quick check in console.) Also, as a tip: people can help you out more if you post the actual info returned instead of a paraphrase of it. On Aug 23, 8:49 pm, Mike Jowee <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> Hi, > I do have the ":joins => :users_emails" > > User.find(:all, :conditions => ["name = ''joe'' and email > ''...@foo.com"], :joins => :users_emails] > > But when I look at the SQL being generated, it looks like a join isn''t > taking place. The actual error I get is that it cannot find the > attribute ''email''. > > Any help would be greatly appreciated. > > > > Jodi Showers wrote: > > Mike - > > > On 23-Aug-08, at 10:29 PM, Mike Jowee wrote: > > >> I tried the following, but it did not work. I think I''mmissing > >> something: > > >> User.find(:all, :conditions => ["name = ''joe'' and email > >> ''...@foo.com"], :joins => :users_emails] > > >> It didn''t seem to generate the join properly. > > >> Any help would be greatly appreciated. > > > the :joins key takes either a string or the name of the association > > (like the :include param). > > > So take a look at your User model, where it references UserEmail > > > It likely reads something like: > > > has_many :user_emails > > > if that''s the case then the join will read ":join => :user_emails". > > > Jodi > > -- > 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@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
hi, UsersEmail.find(:all, :conditions => ["email = ''joe-hcDgGtZH8xNBDgjK7y7TUQ@public.gmane.org'' "]) Returns me an array of all users_emails rows which have email ''joe-hcDgGtZH8xNBDgjK7y7TUQ@public.gmane.org'' The problem is I need to also need to do a select based on users.name. Fred suggested doing a :join using with the syntax ":join => :users_emails" But the following did not work. When I look at the SQL rails tried to generate, it did not seem to do the join. User.find(:all, :conditions => ["name = ''joe'' and email ''foo-+LBmYUDmh58@public.gmane.org"], :joins => :users_emails] The actual error I get is "ActiveRedocrd::StatementInvalid: Mysql::Error: #42S22Unknown column ''email'' in ''whre clause'': SELECT * FROM users users_emails WHERE users_emails (email = ''joe'') Any help would be greatly appreciated. smeade wrote:> Mike, > What does: > > UsersEmail.find(:all, :conditions => ["email = ''joe-hcDgGtZH8xNBDgjK7y7TUQ@public.gmane.org'' "]) > > return? (Just do a quick check in console.) > > Also, as a tip: people can help you out more if you post the actual > info returned instead of a paraphrase of it. > > > On Aug 23, 8:49�pm, Mike Jowee <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>-- 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@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Frederick Cheung
2008-Aug-24 12:02 UTC
Re: Question about ActiveRecord assoication and :join
On 24 Aug 2008, at 12:48, Mike Jowee <rails-mailing-list@andreas- s.net> wrote:> > hi, > UsersEmail.find(:all, :conditions => ["email = ''joe-hcDgGtZH8xNBDgjK7y7TUQ@public.gmane.org'' "]) > Returns me an array of all users_emails rows which have email > ''joe-hcDgGtZH8xNBDgjK7y7TUQ@public.gmane.org'' > > The problem is I need to also need to do a select based on users.name. > Fred suggested doing a :join using with the syntax ":join => > :users_emails" > > But the following did not work. When I look at the SQL rails tried to > generate, it did not seem to do the join. > User.find(:all, :conditions => ["name = ''joe'' and email > ''foo-+LBmYUDmh58@public.gmane.org"], :joins => :users_emails] > > The actual error I get is "ActiveRedocrd::StatementInvalid: > Mysql::Error: #42S22Unknown column ''email'' in ''whre clause'': SELECT * > FROM users users_emails WHERE users_emails (email = ''joe'') >It''s very unhelpful when you type error messages in, introducing errors in the process rather than just copy and pasting them. Be sure that what you pass to joins is precisely the name of the association (ie don''t pluralize it unless it''s a plural association etc) Fred> Any help would be greatly appreciated. > > smeade wrote: >> Mike, >> What does: >> >> UsersEmail.find(:all, :conditions => ["email = ''joe-hcDgGtZH8xNBDgjK7y7TUQ@public.gmane.org'' "]) >> >> return? (Just do a quick check in console.) >> >> Also, as a tip: people can help you out more if you post the actual >> info returned instead of a paraphrase of it. >> >> >> On Aug 23, 8:49╴pm, Mike Jowee <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> > > -- > 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@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Hi, The name of the assoication passed into the join is exactly the same as the one defined in the model class. This is the exact error I get, the Join is not being generated in SQL.: ActiveRecord::StatementInvalid: Mysql::Error: #42S22Unknown column ''email'' in ''w here clause'': SELECT * FROM users users_emails WHERE (email =''joe'') Frederick Cheung wrote:> On 24 Aug 2008, at 12:48, Mike Jowee <rails-mailing-list@andreas- > s.net> wrote: > >> But the following did not work. When I look at the SQL rails tried to >> generate, it did not seem to do the join. >> User.find(:all, :conditions => ["name = ''joe'' and email >> ''foo-+LBmYUDmh58@public.gmane.org"], :joins => :users_emails] >> >> The actual error I get is "ActiveRedocrd::StatementInvalid: >> Mysql::Error: #42S22Unknown column ''email'' in ''whre clause'': SELECT * >> FROM users users_emails WHERE users_emails (email = ''joe'') >> > > It''s very unhelpful when you type error messages in, introducing > errors in the process rather than just copy and pasting them. Be sure > that what you pass to joins is precisely the name of the association > (ie don''t pluralize it unless it''s a plural association etc) > > 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?hl=en -~----------~----~----~----~------~----~------~--~---
Frederick Cheung
2008-Aug-24 12:36 UTC
Re: Question about ActiveRecord assoication and :join
On 24 Aug 2008, at 13:18, Mike Jowee <rails-mailing-list@andreas- s.net> wrote:> > Hi, > The name of the assoication passed into the join is exactly the same > as the one defined in the model class. >:joins can work in two ways: association names or SQL fragments. It looks like rails thinks you mean the latter when you mean the former. I don''t recall precisely what criteria is used but if you are using strings then make sure you are using symbols Fred> This is the exact error I get, the Join is not being generated in > SQL.: > ActiveRecord::StatementInvalid: Mysql::Error: #42S22Unknown column > ''email'' in ''w > here clause'': SELECT * FROM users users_emails WHERE (email =''joe'') > > Frederick Cheung wrote: >> On 24 Aug 2008, at 12:48, Mike Jowee <rails-mailing-list@andreas- >> s.net> wrote: >> >>> But the following did not work. When I look at the SQL rails >>> tried to >>> generate, it did not seem to do the join. >>> User.find(:all, :conditions => ["name = ''joe'' and email >>> ''foo-+LBmYUDmh58@public.gmane.org"], :joins => :users_emails] >>> >>> The actual error I get is "ActiveRedocrd::StatementInvalid: >>> Mysql::Error: #42S22Unknown column ''email'' in ''whre clause'': >>> SELECT * >>> FROM users users_emails WHERE users_emails (email = ''joe'') >>> >> >> It''s very unhelpful when you type error messages in, introducing >> errors in the process rather than just copy and pasting them. Be sure >> that what you pass to joins is precisely the name of the association >> (ie don''t pluralize it unless it''s a plural association etc) >> >> 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?hl=en -~----------~----~----~----~------~----~------~--~---