I have two tables, media and tour_locations, related through a join table. It''s not a pure join, because I need a sequence number on the relationship, so the join also has an id. When I access "aMedium.tour_locations", the reported id''s for the TourLocation''s are the id''s from the join table, not the real id of the tour_locations row. The same for "aTourLocation.media". In the sqlite3 console, the keys and ids are all correct. How can I fix this? Ruby 1.8.7, Rails 2.3.5, sqlite3 3.6.12 Here is an abbreviated console dialog. MediaTourLocation(25) relates Medium(16) to TourLocation(6). =====>> tl = TourLocation.find(6)>> tl.media[... #<Medium id: 25, url: "http://www.li...> ...]>> MediaTourLocation.find_all_by_tour_location_id(6)[... #<MediaTourLocation id: 25, medium_sequence: 0, medium_id: 16, tour_location_id: 6> ...]>> med = Medium.find(16) >> med.tour_locations[#<TourLocation id: 25, locDescription: ...>] ===== The Medium and the TourLocation report each other as id: 25. That''s wrong; neither table has a row with that id. My web pages crash. The habtm declarations and the schema appear at the end of this message. You''ll see I''ve been thrashing class TourLocation: =====class TourLocation < ActiveRecord::Base has_and_belongs_to_many :media, :join_table => "media_tour_locations", :readonly => false class Medium: =====class Medium < ActiveRecord::Base has_and_belongs_to_many :tour_locations, :join_table => "media_tour_locations", :readonly => false Schema: ===== create_table "media", :force => true do |t| t.string "url", :null => false t.string "title", :default => "" t.text "description", :default => "" t.string "media_type", :default => "image", :null => false end create_table "media_tour_locations", :force => true do |t| t.integer "medium_sequence" t.integer "medium_id" t.integer "tour_location_id" end create_table "tour_locations", :force => true do |t| t.text "locDescription", :default => "Enter description", :null => false 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-/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 10 August 2010 17:09, Fritz Anderson <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote:> I have two tables, media and tour_locations, related through a join > table. It''s not a pure join, because I need a sequence number on the > relationship, so the join also has an id. When I access > "aMedium.tour_locations", the reported id''s for the TourLocation''s are > the id''s from the join table, not the real id of the tour_locations row. > The same for "aTourLocation.media". In the sqlite3 console, the keys and > ids are all correct.[snip]> class TourLocation: > =====> class TourLocation < ActiveRecord::Base > has_and_belongs_to_many :media, > :join_table => "media_tour_locations", > :readonly => false > > class Medium: > =====> class Medium < ActiveRecord::Base > has_and_belongs_to_many :tour_locations, > :join_table => "media_tour_locations", > :readonly => false > > Schema: > =====> create_table "media", :force => true do |t| > t.string "url", :null => false > t.string "title", :default => "" > t.text "description", :default => "" > t.string "media_type", :default => "image", :null => false > end > > create_table "media_tour_locations", :force => true do |t| > t.integer "medium_sequence" > t.integer "medium_id" > t.integer "tour_location_id" > end > > create_table "tour_locations", :force => true do |t| > t.text "locDescription", :default => "Enter description", > :null => false > endThe has_and_belongs_to_many assumes that the join table doesn''t have a column called ''id'' -- you''ll see that the SQL it generates just SELECTs an ''id'' column, so it''s (erroneously) picking up the one from the join table in your situation. If your join table is really a fully-fledged model of its own, then you need to bring it into the association explicitly, and use the :through option to connect media and tour_locations: class Medium < ActiveRecord::Base has_many :media_tour_locations has_many :tour_locations, :through => :media_tour_locations end class MediaTourLocation < ActiveRecord::Base belongs_to :medium belongs_to :tour_location end class TourLocation < ActiveRecord::Base has_many :media_tour_locations has_many :media, :through => :media_tour_locations end Chris -- 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.
Chris Mear wrote:> The has_and_belongs_to_many assumes that the join table doesn''t have a > column called ''id'' -- you''ll see that the SQL it generates just > SELECTs an ''id'' column, so it''s (erroneously) picking up the one from > the join table in your situation. > > If your join table is really a fully-fledged model of its own, then > you need to bring it into the association explicitly, and use the > :through option to connect media and tour_locations:... Thanks! This was exactly what I needed to know. Further, it greatly simplified my access to subsets of the has_many :media relationship: has_many :images, :through => :media_tour_locations, :source => :medium, :conditions => "media_type = ''image''", :order => ''media_tour_locations.medium_sequence'' ... whereby I can use aTourLocation.images and get the images back in the sequence specified for that relationship. I _am_ relying on the :order option to produce a correct ORDER BY clause. For Rails 2.3.5 and sqlite3 3.6.12, the generated SQL looks right, and I get the expected results. Will this hold up with different versions and back ends, or am I relying on an implementation detail? — F -- 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-/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 10 August 2010 23:02, Fritz Anderson <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote:> Chris Mear wrote: >> The has_and_belongs_to_many assumes that the join table doesn''t have a >> column called ''id'' -- you''ll see that the SQL it generates just >> SELECTs an ''id'' column, so it''s (erroneously) picking up the one from >> the join table in your situation. >> >> If your join table is really a fully-fledged model of its own, then >> you need to bring it into the association explicitly, and use the >> :through option to connect media and tour_locations: > ... > > Thanks! This was exactly what I needed to know. > > Further, it greatly simplified my access to subsets of the has_many > :media relationship: > > has_many :images, > :through => :media_tour_locations, > :source => :medium, > :conditions => "media_type = ''image''", > :order => ''media_tour_locations.medium_sequence'' > > ... whereby I can use aTourLocation.images and get the images back in > the sequence specified for that relationship. > > I _am_ relying on the :order option to produce a correct ORDER BY > clause. For Rails 2.3.5 and sqlite3 3.6.12, the generated SQL looks > right, and I get the expected results. > > Will this hold up with different versions and back ends, or am I relying > on an implementation detail?Yeah, as far as I know, Rails doesn''t do too much messing around with what you pass in the :order option. So as long as you fully qualify it by including the table name (as you have), it should be solid. Of course, there''s no substitute for writing some tests to make sure! Chris -- 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.