Habtm gets foreign key from wrong column

Rails
#1

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

#2

On 10 August 2010 17:09, Fritz A. [email protected] 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]

has_and_belongs_to_many :tour_locations,
end
end
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:

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

#3

Chris M. 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
#4

On 10 August 2010 23:02, Fritz A. [email protected] wrote:

… 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