Deleting join association of has_many :through

Rails
1

Hi,
i have a situation similar to this:

class User
has_many :favorites
has_many :colors, :through => :favorites
end

class Color
has_many :favorites
has_many :users, :through => :favorites
end

class Favorite
belongs_to :user
belongs_to :color
end

When i need to destroy a favorite association, i find it like this:
fav = user.favorites.find_by_color_id(color_id])

but i cannot destroy it:
calling “fav.destroy” raises a SQL error, because there is no ID field
in table Favorite.

How should that fav be deleted ?

Thanks,
Marcus

2

On 7/21/06, Marcus Ob [email protected] wrote:

has_many :users, :through => :favorites

Posted via http://www.ruby-forum.com/.

Rails mailing list
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails

user.favorites.destroy_all([“color_id=?”, color_id])

3

Marcus Ob wrote:

Hi,
i have a situation similar to this:

class User
has_many :favorites
has_many :colors, :through => :favorites
end

class Color
has_many :favorites
has_many :users, :through => :favorites
end

class Favorite
belongs_to :user
belongs_to :color
end

When i need to destroy a favorite association, i find it like this:
fav = user.favorites.find_by_color_id(color_id])

but i cannot destroy it:
calling “fav.destroy” raises a SQL error, because there is no ID field
in table Favorite.

How should that fav be deleted ?

If you have no :id field, then your join model is broken. You need an
:id in favorites for has_many :through to work right.

–
Josh S.
http://blog.hasmanythrough.com

4

Josh S. wrote:

Marcus Ob wrote:

Hi,
i have a situation similar to this:

class User
has_many :favorites
has_many :colors, :through => :favorites
end

class Color
has_many :favorites
has_many :users, :through => :favorites
end

class Favorite
belongs_to :user
belongs_to :color
end

When i need to destroy a favorite association, i find it like this:
fav = user.favorites.find_by_color_id(color_id])

but i cannot destroy it:
calling “fav.destroy” raises a SQL error, because there is no ID field
in table Favorite.

How should that fav be deleted ?

If you have no :id field, then your join model is broken. You need an
:id in favorites for has_many :through to work right.

–
Josh S.
http://blog.hasmanythrough.com

I have id fields in both master tables (users and colors) and the join
table (favorites) has a pk composed by the two fields user_id+color_id

Is that wrong?
Thanks,
Marcus.

5

On 7/21/06, Marcus Ob [email protected] wrote:

Pat M. wrote:

On 7/21/06, Marcus Ob [email protected] wrote:
user.favorites.destroy_all([“color_id=?”, color_id])

Thank you, but it’s not working

Sorry, it should be delete_all

As far as using composite primary keys…don’t. Sounds like you’re
creating a rails app from scratch, so there’s no reason at all you
should be using them.

Pat

6

Pat M. wrote:

On 7/21/06, Marcus Ob [email protected] wrote:

has_many :users, :through => :favorites

Posted via http://www.ruby-forum.com/.

Rails mailing list
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails

user.favorites.destroy_all([“color_id=?”, color_id])

Thank you, but it’s not working:

I tried
user.favorites.destroy_all([“color_id=?”, color_id])
But it raises an error:
“wrong number of arguments (1 for 0)”.

Without arguments, it wont complain
user.favorites.destroy_all
But i get an error because there is no ID column:
Mysql::Error: #42S22Unknown column ‘id’ in ‘where clause’: DELETE FROM
favorites WHERE id = NULL

The only thing working is
user.colors.clear
which deletes all favorite colors of user, but i don’t want to clear
all records, only a specific one.

Any other ideas?
Thanks,
Marcus.

7

Pat M. wrote:

On 7/21/06, Marcus Ob [email protected] wrote:

Pat M. wrote:

On 7/21/06, Marcus Ob [email protected] wrote:
user.favorites.destroy_all([“color_id=?”, color_id])

Thank you, but it’s not working

Sorry, it should be delete_all

As far as using composite primary keys…don’t. Sounds like you’re
creating a rails app from scratch, so there’s no reason at all you
should be using them.

Pat

So using has_many_through, should i use set the pk to an id field for
table “favorites”, and leave color_id and user_id as fk ?

I was using a composite pk color_id+user_id in favorites table because
it was explained to do so for HABTM.

Thanks,
Marcus.

8

Pat M. wrote:

On 7/21/06, Marcus Ob [email protected] wrote:

As far as using composite primary keys…don’t. Sounds like you’re
creating a rails app from scratch, so there’s no reason at all you
should be using them.

Pat

So using has_many_through, should i use set the pk to an id field for
table “favorites”, and leave color_id and user_id as fk ?

Yes.

You will also probably want to make a multicolumn index on color_id
and user_id for performance reasons. This is really a secondary
concern though and you shouldn’t do that until you see how you’re
actually using the table.

Pat

Pat (and Josh), thank you very much, that solved my
user.favorites.find_by_color_id(color_id).destroy

I am still stuck in the
user.favorites << Color.find_by_id(some_id)
user.save
which does not save any record.

Any suggestions?
Thanks for the great help.
Marcus.

9

On 7/21/06, Marcus Ob [email protected] wrote:

As far as using composite primary keys…don’t. Sounds like you’re
creating a rails app from scratch, so there’s no reason at all you
should be using them.

Pat

So using has_many_through, should i use set the pk to an id field for
table “favorites”, and leave color_id and user_id as fk ?

Yes.

You will also probably want to make a multicolumn index on color_id
and user_id for performance reasons. This is really a secondary
concern though and you shouldn’t do that until you see how you’re
actually using the table.

Pat

10

On 7/21/06, Marcus Ob [email protected] wrote:

table “favorites”, and leave color_id and user_id as fk ?
Pat (and Josh), thank you very much, that solved my

–
Posted via http://www.ruby-forum.com/.

Rails mailing list
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails

disclaimer: I still haven’t used has_many :through yet. Maybe the
code you showed is supposed to work, I’m not sure.

Anyway, it ought to be
user.favorites << Favorite.new(:color => Color.find(some_id))

If you’re just creating the favorite at that point in time and won’t
be using the favorites method, you should just do
Favorite.create(:user => user, :color => Color.find(some_id))

You might be able to do
user.colors << Color.find(some_id)

but again, since that would rely on has_many :through, I’m not sure.

Pat

11

Marcus Ob wrote:

Pat (and Josh), thank you very much, that solved my
user.favorites.find_by_color_id(color_id).destroy

All tables in Rails except for habtm need a single column pk. You can
create indexes as you will, but the pk must be a single column, and if
you can go with the default of an auto-inc int named :id things will be
much easier for you.

I am still stuck in the
user.favorites << Color.find_by_id(some_id)
user.save
which does not save any record.

That’s just the way has_many :through is. Read this:
http://blog.hasmanythrough.com/articles/2006/04/17/join-models-not-proxy-collections

–
Josh S.
http://blog.hasmanythrough.com

12

Josh S. wrote:

Marcus Ob wrote:

Pat (and Josh), thank you very much, that solved my
user.favorites.find_by_color_id(color_id).destroy

All tables in Rails except for habtm need a single column pk. You can
create indexes as you will, but the pk must be a single column, and if
you can go with the default of an auto-inc int named :id things will be
much easier for you.

I am still stuck in the
user.favorites << Color.find_by_id(some_id)
user.save
which does not save any record.

That’s just the way has_many :through is. Read this:
http://blog.hasmanythrough.com/articles/2006/04/17/join-models-not-proxy-collections

–
Josh S.
http://blog.hasmanythrough.com

I see, thank you Josh.
And thank you Pat.
I guess perhaps this is the code to add a new favorite:

fav=Favorite.new(:user_id=>user.id, :color_id=>some_color.id)
fav.save

Best regards,
Marcus.