I have the following model structure setup. class User < ActiveRecord::Base end class Parent < User has_many :relationships has_many :children, :class_name => "Student", :through => :relationships, :conditions => "related_as = ''parent''" end class Student < User has_many :relationships has_many :parents, :through => :relationships, :conditions => "related_as = ''child''" end class Relationship < ActiveRecord::Base belongs_to :parent, :foreign_key => :user_id belongs_to :student, :foreign_key => :user_id end class CreateRelationships < ActiveRecord::Migration def self.up create_table :relationships do |t| t.integer :user_id, :null => false t.integer :related_user_id, :null => false t.string :status, :null => false t.string :related_as, :limit => 100, :null => false # e.g. ''parent'' or ''child'' or ''friend'' t.timestamps end end def self.down drop_table :relationships end end -------------------------------------------------------------------- I am trying to use the Relationship model to store many different kinds of relationships like parent/child, friend/friend, husband/wife etc. With the above setup when I go to console I get the following>> c = Student.find(144)=> <Successfully get the Student object>>> c.parentsActiveRecord::StatementInvalid: Mysql::Error: Unknown column ''relationships.student_id'' in ''where clause'': SELECT `users`.* FROM `users` INNER JOIN ` relationships` ON `users`.id = `relationships`.user_id WHERE ((`relationships`.student_id = 144) AND ((related_as = ''child''))) AND ( (`users`.`type ` = ''Parent'' ) ) The problem is in WHERE clause `relationships`.student_id - I don''t have a student_id in that model (trying to keep it generic) and instead want to use the related_user_id column from relationships table. I have played around with the :foreign_key attribute on belongs_to setup for relationships table and the way I have it setup now generates the first part of the SQL correctly `users`.id `relationships`.user_id . How do I change the `relationships`.student_id to use `relationships`.related_user_id instead? Or is there a better/ different way to do what I am trying to accomplish? Do I have to resort to a Polymorphic setup here (quick, half hearted try there gives some other issues that I need to look deeper into if I have to go down that route)? I am on 2.3.8. Thanks for your help. -S -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On Jan 14, 7:49 pm, skt <stibre...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> I have the following model structure setup. > > class User < ActiveRecord::Base > end > > class Parent < User > has_many :relationships > has_many :children, > :class_name => "Student", > :through => :relationships, > :conditions => "related_as = ''parent''" > end > > class Student < User > has_many :relationships > has_many :parents, > :through => :relationships, > :conditions => "related_as = ''child''" > end > > class Relationship < ActiveRecord::Base > belongs_to :parent, :foreign_key => :user_id > belongs_to :student, :foreign_key => :user_id > end >This isn''t going to do what you want - now you''ve got two belongs_to associations that both want to use the ''user_id'' column to store their foreign key.> > ActiveRecord::StatementInvalid: Mysql::Error: Unknown column > ''relationships.student_id'' in ''where clause'': SELECT `users`.* FROM > `users` INNER JOIN ` > relationships` ON `users`.id = `relationships`.user_id WHERE > ((`relationships`.student_id = 144) AND ((related_as = ''child''))) AND > ( (`users`.`type > ` = ''Parent'' ) ) > > The problem is in WHERE clause `relationships`.student_id - I don''t > have a student_id in that model (trying to keep it generic) and > instead want to use the related_user_id column from relationships > table. I have played around with the :foreign_key attribute on > belongs_to setup for relationships table and the way I have it setup > now generates the first part of the SQL correctly `users`.id > `relationships`.user_id . > > How do I change the `relationships`.student_id to use > `relationships`.related_user_id instead? Or is there a better/ > different way to do what I am trying to accomplish? Do I have to > resort to a Polymorphic setup here (quick, half hearted try there > gives some other issues that I need to look deeper into if I have to > go down that route)?You''re going to need to pass :foreign_key to the has_many :relationships calls as well, as that''s whats generating the bad SQL (a standard has_many :relationships on Student will look for relationships.student_id) You''ll probably want something more like this (not tested): class Parent < User has_many :relationships, :foreign_key => ''user_id'' has_many :children, :class_name => "Student", :through => :relationships, :conditions => "related_as = ''parent''", :source => :student end class Student < User has_many :relationships, :foreign_key => ''related_user_id'' has_many :parents, :through => :relationships, :conditions => "related_as = ''child''", end class Relationship < ActiveRecord::Base belongs_to :parent, :foreign_key => :user_id belongs_to :student, :foreign_key => :related_user_id end Note that you *can* declare duplicate belongs_to on the same foreign key if needed, so you could add: belongs_to :husband, :foreign_key => ''user_id'' belongs_to :wife, :foreign_key => ''related_user_id'' The friend/friend case is messier - there are some good tutorials on different ways to accomplish it with has_many :through. --Matt Jones -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@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 Jan 15, 8:34 am, Matt Jones <al2o...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On Jan 14, 7:49 pm,skt<stibre...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > > > > > > > > > I have the following model structure setup. > > > class User < ActiveRecord::Base > > end > > > class Parent < User > > has_many :relationships > > has_many :children, > > :class_name => "Student", > > :through=> :relationships, > > :conditions => "related_as = ''parent''" > > end > > > class Student < User > > has_many :relationships > > has_many :parents, > > :through=> :relationships, > > :conditions => "related_as = ''child''" > > end > > > class Relationship < ActiveRecord::Base > > belongs_to :parent, :foreign_key => :user_id > > belongs_to :student, :foreign_key => :user_id > > end > > This isn''t going to do what you want - now you''ve got two belongs_to > associations that both want to use the ''user_id'' column to store their > foreign key. > > > > > > > > > > > > > ActiveRecord::StatementInvalid: Mysql::Error: Unknown column > > ''relationships.student_id'' in ''where clause'': SELECT `users`.* FROM > > `users` INNER JOIN ` > > relationships` ON `users`.id = `relationships`.user_id WHERE > > ((`relationships`.student_id = 144) AND ((related_as = ''child''))) AND > > ( (`users`.`type > > ` = ''Parent'' ) ) > > > The problem is in WHERE clause `relationships`.student_id - I don''t > > have a student_id in that model (trying to keep it generic) and > > instead want to use the related_user_id column from relationships > > table. I have played around with the :foreign_key attribute on > > belongs_to setup for relationships table and the way I have it setup > > now generates the first part of the SQL correctly `users`.id > > `relationships`.user_id . > > > How do I change the `relationships`.student_id to use > > `relationships`.related_user_id instead? Or is there a better/ > > different way to do what I am trying to accomplish? Do I have to > > resort to a Polymorphic setup here (quick, half hearted try there > > gives some other issues that I need to look deeper into if I have to > > go down that route)? > > You''re going to need to pass :foreign_key to thehas_many:relationships calls as well, as that''s whats generating the > bad SQL (a standardhas_many:relationships on Student will look for > relationships.student_id) > > You''ll probably want something more like this (not tested): > > class Parent < User > has_many :relationships, :foreign_key => ''user_id'' > has_many :children, > :class_name => "Student", > :through=> :relationships, > :conditions => "related_as = ''parent''", > :source => :student > end > class Student < User > has_many :relationships, :foreign_key => ''related_user_id'' > has_many :parents, > :through=> :relationships, > :conditions => "related_as = ''child''", > end > class Relationship < ActiveRecord::Base > belongs_to :parent, :foreign_key => :user_id > belongs_to :student, :foreign_key => :related_user_id > end > > Note that you *can* declare duplicate belongs_to on the same foreign > key if needed, so you could add: > > belongs_to :husband, :foreign_key => ''user_id'' > belongs_to :wife, :foreign_key => ''related_user_id'' > > The friend/friend case is messier - there are some good tutorials on > different ways to accomplish it withhas_many:through. > > --Matt JonesThanks Matt. I plan to store two records per relationship - one for the person who makes the request and one for the person of whom the request is made - may not be that useful for parent/child relationship but will be very useful for friend/friend relationship. With that in mind the user_id field will contain the parent''s id for one of the records and the child''s id for the other record with the related_as field reflecting the relationship - ''parent'' in one case and ''child'' in another. With the way you have setup it doesn''t look like I can do that - you seem to assume one record per relationship - let me know if I am misunderstanding. Not ideal from compactness perspective, but this dual record entry becomes even more important when I have friend requests and need to keep track of statuses/rejections/cancelations etc - basing off of some examples I have seen. I will Google around but if you know of some good examples for the friend/friend case with has_many :through that you mention above, could you please point me to them? Thanks again for your help. -S -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@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 Jan 17, 9:30 am, skt <stibre...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On Jan 15, 8:34 am, Matt Jones <al2o...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > > > > > > > > > On Jan 14, 7:49 pm,skt<stibre...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > > I have the following model structure setup. > > > > class User < ActiveRecord::Base > > > end > > > > class Parent < User > > > has_many :relationships > > > has_many :children, > > > :class_name => "Student", > > > :through=> :relationships, > > > :conditions => "related_as = ''parent''" > > > end > > > > class Student < User > > > has_many :relationships > > > has_many :parents, > > > :through=> :relationships, > > > :conditions => "related_as = ''child''" > > > end > > > > class Relationship < ActiveRecord::Base > > > belongs_to :parent, :foreign_key => :user_id > > > belongs_to :student, :foreign_key => :user_id > > > end > > > This isn''t going to do what you want - now you''ve got two belongs_to > > associations that both want to use the ''user_id'' column to store their > > foreign key. > > > > ActiveRecord::StatementInvalid: Mysql::Error: Unknown column > > > ''relationships.student_id'' in ''where clause'': SELECT `users`.* FROM > > > `users` INNER JOIN ` > > > relationships` ON `users`.id = `relationships`.user_id WHERE > > > ((`relationships`.student_id = 144) AND ((related_as = ''child''))) AND > > > ( (`users`.`type > > > ` = ''Parent'' ) ) > > > > The problem is in WHERE clause `relationships`.student_id - I don''t > > > have a student_id in that model (trying to keep it generic) and > > > instead want to use the related_user_id column from relationships > > > table. I have played around with the :foreign_key attribute on > > > belongs_to setup for relationships table and the way I have it setup > > > now generates the first part of the SQL correctly `users`.id > > > `relationships`.user_id . > > > > How do I change the `relationships`.student_id to use > > > `relationships`.related_user_id instead? Or is there a better/ > > > different way to do what I am trying to accomplish? Do I have to > > > resort to a Polymorphic setup here (quick, half hearted try there > > > gives some other issues that I need to look deeper into if I have to > > > go down that route)? > > > You''re going to need to pass :foreign_key to thehas_many:relationships calls as well, as that''s whats generating the > > bad SQL (a standardhas_many:relationships on Student will look for > > relationships.student_id) > > > You''ll probably want something more like this (not tested): > > > class Parent < User > > has_many :relationships, :foreign_key => ''user_id'' > > has_many :children, > > :class_name => "Student", > > :through=> :relationships, > > :conditions => "related_as = ''parent''", > > :source => :student > > end > > class Student < User > > has_many :relationships, :foreign_key => ''related_user_id'' > > has_many :parents, > > :through=> :relationships, > > :conditions => "related_as = ''child''", > > end > > class Relationship < ActiveRecord::Base > > belongs_to :parent, :foreign_key => :user_id > > belongs_to :student, :foreign_key => :related_user_id > > end > > > Note that you *can* declare duplicate belongs_to on the same foreign > > key if needed, so you could add: > > > belongs_to :husband, :foreign_key => ''user_id'' > > belongs_to :wife, :foreign_key => ''related_user_id'' > > > The friend/friend case is messier - there are some good tutorials on > > different ways to accomplish it withhas_many:through. > > > --Matt Jones > > Thanks Matt. I plan to store two records per relationship - one for > the person who makes the request and one for the person of whom the > request is made - may not be that useful for parent/child relationship > but will be very useful for friend/friend relationship. With that in > mind the user_id field will contain the parent''s id for one of the > records and the child''s id for the other record with the related_as > field reflecting the relationship - ''parent'' in one case and ''child'' > in another. With the way you have setup it doesn''t look like I can do > that - you seem to assume one record per relationship - let me know if > I am misunderstanding. Not ideal from compactness perspective, but > this dual record entry becomes even more important when I have friend > requests and need to keep track of statuses/rejections/cancelations > etc - basing off of some examples I have seen. > > I will Google around but if you know of some good examples for the > friend/friend case with has_many :through that you mention above, > could you please point me to them? > > Thanks again for your help. > > -SI finally got it to work. Thanks Matt for pointing me in the right direction. For people who stumble on this in the future, here is how I set it up - hope it helps someone down the road. class User < ActiveRecord::Base end class Parent < User has_many :relationships, :foreign_key => :user_id has_many :children, :through => :relationships, :class_name => "Student", :conditions => "related_as = ''parent''" end class Student < User has_many :relationships, :foreign_key => :user_id has_many :parents, :through => :relationships, :conditions => "related_as = ''child''" end class Relationship < ActiveRecord::Base belongs_to :parent, :foreign_key => :related_user_id belongs_to :child, :class_name => "Student", :foreign_key => :related_user_id 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@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.
Seemingly Similar Threads
- ActiveRecord::Base.transaction - SystemStackError - stack level too deep:
- Self-referential has_many :through relationship
- Once I added this HABTM, one of my 'through' relationships, on a non-habtm model, seems to have broke?
- has_many and belongs_to with non-primary foreign keys
- Inserting the parent Id to the child table