has_many :through

class Media < ActiveRecord::Base
has_many :prices
has_many :price_codes, :through =:prices
end

class Price < ActiveRecord::Base
belongs_to :media
belongs_to :price_code
end

class PriceCode < ActiveRecord::Base
has_many :prices
has_many :medias, :through =:prices
end

Based this exmaple off a recipr in the Rails Recipies Cookbook.

When I manually add data to the underlying tables, I can correctly
display the data through by navigating through the object model.

What I'm having trouble figuring out is how to delete Price records form
the association table.

Within one of my forms I have the following:

<%= link_to 'Destroy', { :action ='destroy_price', :id =@media.id,
:price_code_id =>price_code.id }, :confirm ='Are you sure?', :post =
true %>

Add here is the destroy_price action in the controller:

def destroy_price
Media.find(params[:id]).prices.find(params[:price_code_id]).destroy
redirect_to :action ='show', :id =params[:id]
end

When I call the action the following error is returned:

ActiveRecord::StatementInvalid in MediaController#destroy_price

Mysql::Error: #42S22Unknown column 'prices.id' in 'where clause': SELECT
* FROM prices WHERE (prices.id = '1' AND (prices.media_id = 82)) LIMIT
1

For this example I'm trying to delete the prices record with media_id of
82 and price_code_id of 1. For some reason the sql that is getting
generated by ActiveRecord is using prices.id = '1' rather than
prices.price_code_id = 1.

Not really sure what to do next to attempt to get this to work...

Any ideas would be greatly appreciated.

From an iRB session I can find the Prices record, but trying to destroy
it produces this error. I don't know why it's looking for an id since
there is no id in the prices table:

>>Price.find_by_media_id_and_price_code_id(82,2)
=#"82",
"updated_at"=>"2006-08-08 18:04:35", "price_code_id"=>"2",
"created_at"=>"2006-08-08 18:04:35"}>

>>Price.find_by_media_id_and_price_code_id(82,2).destroy
ActiveRecord::StatementInvalid: Mysql::Error: Unknown column 'id' in
'where clause': DELETE FROM prices WHERE id = NULL

You NEED an id in the join model. has_many :through won't work without
it.

Josh Susser http://blog.hasmanythrough.com/