Self-referential many-to-many relationship
see also HowToUseManyToManyAgainstASingleTable
if you are interested in using has_many :through instead of has_and_belongs_to_many see
In an online store the concept of cross sell products is common. When looking at product alpha the web page might have “may we also suggest products beta and gamma”. And on the beta page the store might be cross selling alpha and gamma. This is a self-referential many-to-many relationship on the products table. I imagine that this type of relationship could come up in many situations. Here is how I accomplished this for the cross sell problem.
I made two tables: products and a join table called cross_sells. I used cs_position for ordering the cross sell products within a particular product. I didn’t use the magic field name “position” because I was worried about having a name collision somewhere unexpected.
CREATE TABLE products ( id INT NOT NULL AUTO_INCREMENT, name varchar(255) NOT NULL, PRIMARY KEY (id) ); INSERT INTO products VALUES (1, 'alpha'); INSERT INTO products VALUES (2, 'beta'); INSERT INTO products VALUES (3, 'gamma'); CREATE TABLE cross_sells ( product_id INT NOT NULL, cross_sell_product_id INT NOT NULL, cs_position INT NOT NULL, PRIMARY KEY (product_id, cross_sell_product_id), UNIQUE KEY pos (cs_position) ); #cross sell beta(2) and gamma(3) on alpha(1) INSERT INTO cross_sells VALUES (1,2,1); INSERT INTO cross_sells VALUES (1,3,2);
My product model in product.rb was this
class Product < ActiveRecord::Base
has_and_belongs_to_many :cross_sell_products,
:join_table => 'cross_sells',
:foreign_key => 'product_id',
:association_foreign_key => 'cross_sell_product_id',
:class_name => 'Product',
:order => 'cs_position'
has_and_belongs_to_many :cross_seller_products,
:join_table => 'cross_sells',
:foreign_key => 'cross_sell_product_id',
:association_foreign_key => 'product_id',
:class_name => 'Product'
end
My view template in product.rhtml could easily list links to the cross sell products
<h2>Product: <%= @product.name %></h2>
<h4>Cross Sell Products</h4>
<p>May we also suggest:</p>
<% @product.cross_sell_products.each do |cross_sell_product|%>
<%= link_to cross_sell_product.name, :action => 'product',
:id => cross_sell_product.id %><br />
<% end %>
A slight alternative
There was some discussion on this subject on the SitePoint forums.
The original thread can be seen here
Here, the problem was similar, with many users knowing many users. In this problem if person A knows B then automatically person B knows A. That is the difference when comparing with the cross sell example above. If A is cross selling B it is not necessarily true that B is cross selling A.
My solution was similar, but slightly different…it involved just a single HABTM statement and using callbacks to make sure the relationship was created both ways.
I’ve also used an alias method however looking at the solution above, this could probably be naming the relationship the same as the alias method (known_users) and using the :class attribute to make sure it is still a user class.
Example user table:
CREATE TABLE users ( id INT, name VARCHAR(20), PRIMARY KEY(id) );
A join table:
CREATE TABLE users_known_users ( user_id INT, known_user_id INT, PRIMARY KEY(user_id, known_user_id) );
The model:
class User < ActiveRecord::Base
has_and_belongs_to_many :users,
:join_table => 'users_known_users',
:foreign_key => 'known_user_id',
:association_foreign_key => 'user_id',
:after_add => :create_reverse_association,
:after_remove => :remove_reverse_association
def known_users
self.users
end
private
def create_reverse_association(associated_user)
associated_user.known_users << self unless associated_user.known_users.include?(self)
end
def remove_reverse_association(associated_user)
associated_user.known_users.delete(self) if associated_user.known_users.include?(self)
end
end
Another slight alternative
Example user table:
CREATE TABLE users ( id INT, name VARCHAR(20), PRIMARY KEY(id) );
A join table:
CREATE TABLE `relationships` ( `src_user` INT, `dest_user` INT, `relation` VARCHAR(20), PRIMARY KEY (`src_user`,`dest_user`) );
The model:
class User < ActiveRecord::Base
has_and_belongs_to_many :friends,
:class_name => 'User',
:join_table => 'relationships',
:foreign_key => 'src_user',
:association_foreign_key => 'dest_user',
:conditions => 'relation = \'friend\'',
:insert_sql => 'INSERT INTO relationships
(src_user, dest_user, relation) values
(#{record.id}, #{id}, \'friend\')'
end
So you can do @user.friends << @another_user
-Strass
Another Comment
Playing with some of the ideas on this page, I am setting up a language learning site at idiomz.org where English idioms are listed, and their relations to each other are captured in a self-referencing HABTM. Using the relation attribute works,(such as “antonym”, “synonym”, “related”), but you cannot set more than one relationship type between idioms. (MySQL will panic because the composite key becomes non-unique. Perhaps making the composite key 3-way between id,id,&relation would work.
has_and_belongs_to_many :antonyms,
:class_name => 'MasterItem',
:join_table => 'relations',
:foreign_key => 'related_master_item_id',
:association_foreign_key => 'master_item_id',
:conditions => 'relationship_type = \'antonym\'',
:insert_sql => 'INSERT INTO relations
(master_item_id, related_master_item_id, relationship_type) values
(#{record.id}, #{id}, \'antonym\')',
:after_add => :create_reverse_antonym,
:after_remove => :remove_reverse_antonym
has_and_belongs_to_many :relateds,
:class_name => 'MasterItem',
:join_table => 'relations',
:foreign_key => 'related_master_item_id',
:association_foreign_key => 'master_item_id',
:conditions => 'relationship_type = \'related\'',
:insert_sql => 'INSERT INTO relations
(master_item_id, related_master_item_id, relationship_type) values
(#{record.id}, #{id}, \'related\')',
:after_add => :create_reverse_related,
:after_remove => :remove_reverse_related
In any case, it looks like creating HABTM relations with separate composite tables for each relationship type is the cleaner way to do this.
From the API
“Deprecated: Any additional fields added to the join table will be placed as attributes when pulling records out through has_and_belongs_to_many associations. Records returned from join tables with additional attributes will be marked as ReadOnly (because we can‘t save changes to the additional attrbutes). It‘s strongly recommended that you upgrade any associations with attributes to a real join model (see introduction).”
http://api.rubyonrails.com/classes/ActiveRecord/Associations/ClassMethods.html
Thanks for the great comments Peter, Luke, Strass!
-Kevin Ready
Denying a Self-Referencing Object
Ensure an object cannot reference itself:
validate_on_update :deny_self_reference
...
def deny_self_reference
if self.friends.include?(self)
errors.add(:friends, "cannot include oneself as a friend")
end
end
-CH