Sorry for the long post... I''m having difficulties with accessing my data using has_many through associations. For simplicity, I''ll reference the Agile Web Development example on this topic. Article has_many :readings has_many :users, :through => :readings Readings belongs_to :article, :foreign_key => :article_id belongs_to :user, :foreign_key => :user_id Users has_many :readings has_many :articles, through => readings I''m dealing with legacy data and so the glitch to this set up is that the articles table is has id as the primary key and article_id is another field in the table. I want the readings table to connect to the articles table via article.article_id, not article.id. The end result that I want to see is providing a user (user.id = 1) and then output a report that shows me a list of all of the articles that user 1 has read and tell me each time user 1 read the article. User 1 ===== Article id = 1 =============Reading id = 1 3/23/2008 Reading id = 2 3/14/2008 Reading id = 3 3/7/2008 Article id = 2 =============Reading id = 4 3/15/2008 Reading id = 5 3/3/2008 Right now, the query that I get back is select articles.id from articles left outer join readings readings.article_id = articles.id where user_id = 1 Any ideas on how to change my relationships so that I can connect readings to articles? THANKS!!! -- 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 -~----------~----~----~----~------~----~------~--~---
Becca Girl wrote:> Article > has_many :readings > has_many :users, :through => :readings > > Readings > belongs_to :article, :foreign_key => :article_id > belongs_to :user, :foreign_key => :user_id > > Users > has_many :readings > has_many :articles, through => readingsAdd the foreign keys to the has_many side, too: Article has_many :readings, :foreign_key => :article_id etc -- 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 -~----------~----~----~----~------~----~------~--~---
Mark Bush wrote:> Add the foreign keys to the has_many side, too: > > Article > has_many :readings, :foreign_key => :article_id >I now have this Article has_many :readings, :foreign_key => :article_id has_many :users, :through => :readings Readings belongs_to :article, :foreign_key => :article_id belongs_to :user, :foreign_key => :user_id Users has_many :readings, :foreign_key => :user_id has_many :articles, through => readings But my sql query has not changed. Maybe I need to change the code that generates the query? @articles = Articles.find(:all, :conditions => "user_id = 1", :include => :readings) -- 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 -~----------~----~----~----~------~----~------~--~---
Becca Girl wrote:> But my sql query has not changed. Maybe I need to change the code that > generates the query? > > @articles = Articles.find(:all, :conditions => "user_id = 1", :include > => :readings)Sorry, I wasn''t thinking right. The foreign key specifications on the has_many/belongs_to association refers to the name of the column on the belongs_to side only. Since this column is named correctly anyway you don''t need to specify this on either side. The actual issue here is that you are performing the Rails equivalent of a foreign key lookup and a foreign key must connect to a primary key. In Rails, when handling legacy tables, the primary key of a table is not always the one you want Rails to see as the primary key. In your example, the Article model (presumably using an articles table?) has a database column of id which is the table''s primary key, but from the Rails perspective, the article_id column is the primary key. Use: class Article self.primary_key = :article_id has_many :readings has_many :users, :through => :readings class Reading belongs_to :article belongs_to :user If the same situation occurs in the User class, then do the same thing with that association. -- 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 -~----------~----~----~----~------~----~------~--~---
Mark Bush wrote:> class Article > self.primary_key = :article_id > has_many :readings > has_many :users, :through => :readings > > class Reading > belongs_to :article > belongs_to :user >Thanks Mark. Here''s a funny thing though, the primary key of article is not article_id, it''s actually id. The article_id is actually how these two tables connect to each other. So the primary key needs to be used in other areas of the site, but not in this situation. Again, legacy data is making it a bit more difficult. Any way to tell this to not use id and instead use article_id in this instance? -- 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 -~----------~----~----~----~------~----~------~--~---
This is probably out of my depth, but I''ll blunder in anyway. Could maybe a view rescue you here? If all you need to do is rename some fields, maybe you could create a view & point rails at that? Just a thought... -----Original Message----- From: rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org [mailto:rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org] On Behalf Of Becca Girl Sent: Thursday, March 27, 2008 8:24 AM To: rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org Subject: [Rails] Re: has_many, through, foreign key problems Mark Bush wrote:> class Article > self.primary_key = :article_id > has_many :readings > has_many :users, :through => :readings > > class Reading > belongs_to :article > belongs_to :user >Thanks Mark. Here''s a funny thing though, the primary key of article is not article_id, it''s actually id. The article_id is actually how these two tables connect to each other. So the primary key needs to be used in other areas of the site, but not in this situation. Again, legacy data is making it a bit more difficult. Any way to tell this to not use id and instead use article_id in this instance? -- 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 -~----------~----~----~----~------~----~------~--~---
Becca Girl wrote:> Any way to tell this to not use id and instead use article_id in this > instance?The primary key is a class attribute. You could try adding interfaces to these queries in your Article model where the primary key is temporarily changed. Something like: class Article # association stuff def self.article_id_find *args self.primary_key = :article_id find *args ensure self.primary_key = :id end If this is running under mongrel it should be ok as mongrel is single threaded, but it''s a bit of a kludge. -- 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 -~----------~----~----~----~------~----~------~--~---
Mark Bush wrote:> class Article > # association stuff > > def self.article_id_find *args > self.primary_key = :article_id > find *args > ensure > self.primary_key = :id > end > > If this is running under mongrel it should be ok as mongrel is single > threaded, but it''s a bit of a kludge.I dropped in your code (using my field names), but the query stays the same. Ahead of this method, I have self.primary_key defined since due to legacy data it''s table_id, not id. table structure: table_id (primary key) article_id (links to readings) So my code now looks like this self.primary_key = "table_id" def self.pre_mover_legacy_id_find *args self.primary_key = :article_id find *args ensure self.primary_key = "table_id" end The sql query still tries to join readings.article_id to article.table_id instead of article.article_id. @articles = Articles.find(:all, :conditions => "user_id = 1", :include => :readings) Any guidance on what @articles should look like now or is there something else I should change somewhere else? -- 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 -~----------~----~----~----~------~----~------~--~---
Becca Girl wrote:> @articles = Articles.find(:all, :conditions => "user_id = 1", :include > => :readings)And: @articles = Article. pre_mover_legacy_id_find, :include => :readings ? I''ve now had a chance to test this and it is working ok here. I get: Article Load Including Associations (0.000314) SELECT articles."article_id" AS t0_r0, articles."table_id" AS t0_r1, articles."title" AS t0_r2, articles."created_at" AS t0_r3, articles."updated_at" AS t0_r4, readings."id" AS t1_r0, readings."article_id" AS t1_r1, readings."user_id" AS t1_r2, readings."created_at" AS t1_r3, readings."updated_at" AS t1_r4 FROM articles LEFT OUTER JOIN readings ON readings.article_id = articles.article_id And for: @articles = Article. pre_mover_legacy_id_find :all, :include => :users, :conditions => ["users.id = ?", 1] I get: Article Load Including Associations (0.000344) SELECT articles."article_id" AS t0_r0, articles."table_id" AS t0_r1, articles."title" AS t0_r2, articles."created_at" AS t0_r3, articles."updated_at" AS t0_r4, users."id" AS t1_r0, users."username" AS t1_r1, users."created_at" AS t1_r2, users."updated_at" AS t1_r3 FROM articles LEFT OUTER JOIN readings ON (articles."article_id" = readings."article_id") LEFT OUTER JOIN users ON (users."id" = readings."user_id") WHERE (users.id = 1) -- 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 -~----------~----~----~----~------~----~------~--~---
Mark Bush wrote:> > @articles = Article. pre_mover_legacy_id_find :all, > :include => :users, > :conditions => ["users.id = ?", 1]You''ve been so great! Hopefully you have a little more time. Here are all of my parts and pieces that are throwing an error. Models: class Article has_many :readings has_many :users, :through => :readings self.primary_key = "table_id" def self.article_id_find *args self.primary_key = :article_id find *args ensure self.primary_key = "table_id" end end # class Article class Reading belongs_to :article belongs_to :user end # class Reading class User has_many :readings has_many :articles, :through => :readings end # class User UsersController @articles = Article.article_id_find :all, :include => :users, :conditions => ["users.id = ?", 1] Error: uninitialized constant UsersController::Articles -- 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 -~----------~----~----~----~------~----~------~--~---
Becca Girl wrote:> Error: > uninitialized constant UsersController::ArticlesThis indicates that you are referring to "Articles" (plural) instead of "Article" in the UsersController somewhere and Rails can''t work out what it should be... To be sure the query is working, try the #article_id_find method in the console... -- 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 -~----------~----~----~----~------~----~------~--~---
You need to specify the primary key for the article table. This is REALLY not recommended. Julian On 28/03/2008, at 12:52 AM, Becca Girl wrote:> > Mark Bush wrote: > >> Add the foreign keys to the has_many side, too: >> >> Article >> has_many :readings, :foreign_key => :article_id >> > > > > I now have this > Article > has_many :readings, :foreign_key => :article_id > has_many :users, :through => :readings > > Readings > belongs_to :article, :foreign_key => :article_id > belongs_to :user, :foreign_key => :user_id > > Users > has_many :readings, :foreign_key => :user_id > has_many :articles, through => readings > > > But my sql query has not changed. Maybe I need to change the code > that > generates the query? > > @articles = Articles.find(:all, :conditions => "user_id = 1", :include > => :readings) > -- > 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 -~----------~----~----~----~------~----~------~--~---