Ruby on Rails
HowToCreateASelfReferentialManyToManyRelationship (Version #52)

Self-referential many-to-many relationship

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 %>

-Peter Michaux

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

-Luke Redpath

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

Bath and Shower
Fragrance
Gift Sets
Hair Care
Makeup
Men’s Grooming
Shaving and Hair Removal
Skin Care
Tools and Accessories

Baby Apparel
Baby Bathing & Skin Care
Baby Bedding
Baby Car Seats
Baby Diapering
Baby Feeding
For Moms
Baby Furniture
Baby Gear
Baby Gifts
Baby Health & Baby Care
Nursery Décor
Potty Training
Baby Safety
Baby Strollers

Books – Business & Investing
Books – Children’s Books
Books – Comics & Graphic Novels
Books – Computers & Internet
Books – Cooking, Food & Wine
Books – Entertainment
Books – Gay & Lesbian
Books – Health, Mind & Body
Books – History
Books – Home & Garden
Books – Law
Books – Literature & Fiction
Books – Medicine
Books – Mystery & Thrillers
Books – Nonfiction
Books – Outdoors & Nature
Books – Parenting & Families
Books – Professional & Technical
Books – Reference
Books – Religion & Spirituality
Books – Romance
Books – Science
Books – Science Fiction & Fantasy
Books – Sports
Books – Teens
Books – Travel

Camera & Photo – Accessories
Camera & Photo – Camcorders
Camera & Photo – Digital Cameras
Camera & Photo – Film Cameras
Camera & Photo – Optics
Camera & Photo – Printers & Scanners

Health & Personal Care – Baby & Child Care
Health & Personal Care – Food & Snacks
Health & Personal Care – Health & Personal Care Outlet Store
Health & Personal Care – Health Care
Health & Personal Care – House Supplies
Health & Personal Care – Nutrition & Fitness
Health & Personal Care – Personal Care
Health & Personal Care – Sexual Wellness

Computers – Computer Add-Ons
Computers – Desktops
Computers – Handhelds & PDAs
Computers – Notebooks

Electronics – Accessories & Supplies
Electronics – Audio & Video
Electronics – Camera & Photo
Electronics – Car Electronics
Electronics – Computers & Add-Ons
Electronics – GPS & Navigation
Electronics – Home Automation & Security
Electronics – Office Electronics
Electronics – Service & Replacement Plans

Gourmet Food – Appetizers & Hors d’oeuvres
Gourmet Food – Baking Supplies
Gourmet Food – Beverages
Gourmet Food – Bread
Gourmet Food – Breakfast Foods
Gourmet Food – Candy
Gourmet Food – Cheese
Gourmet Food – Chocolate
Gourmet Food – Coffee & Tea
Gourmet Food – Cookies
Gourmet Food – Dairy Foods & Eggs
Gourmet Food – Desserts
Gourmet Food – Fruits & Vegetables
Gourmet Food – Gourmet Gifts
Gourmet Food – Jams, Jellies & Preserves
Gourmet Food – Meat, Game & Pâtés
Gourmet Food – Oils, Vinegars & Salad Dressings
Gourmet Food – Pasta, Beans, Grains & Rice
Gourmet Food – Prepared Meals
Gourmet Food – Salsas & Condiments
Gourmet Food – Seafood & Caviar
Gourmet Food – Seasonings, Herbs & Spices
Gourmet Food – Snack Food
Gourmet Food – Soups & Stocks

Grocery – Appetizers & Hors D’Oeuvres
Grocery – Baking Supplies
Grocery – Beverages
Grocery – Boxed Meals & Side Dishes
Grocery – Breads
Grocery – Breakfast Foods
Grocery – Canned & Packaged Goods
Grocery – Coffee, Tea & Cocoa
Grocery – Condiments, Sauces & Spreads
Grocery – Dairy & Eggs
Grocery – Desserts & Pastries
Grocery – Fruits & Vegetables
Grocery – Health & Family
Grocery – Herbs, Spices & Seasonings
Grocery – Household Supplies
Grocery – Meats
Grocery – Pasta & Grains
Grocery – Pet Supplies
Grocery – Seafood
Grocery – Snacks, Cookies & Candy

Home & Garden – Backyard Birding
Home & Garden – Bar Tools & Glasses
Home & Garden – Bedding & Bath
Home & Garden – Coffee, Tea & Espresso
Home & Garden – Cook’s Tools & Gadgets
Home & Garden – Cookware & Baking
Home & Garden – Cutlery
Home & Garden – Fresh Flowers & Indoor Plants
Home & Garden – Furniture & Décor
Home & Garden – Gardening Tools
Home & Garden – Grills, Smokers & Outdoor Cooking
Home & Garden – Heating & Lighting
Home & Garden – Kitchen & Table Linens
Home & Garden – Large Appliances
Home & Garden – Leisure & Fitness
Home & Garden – Outdoor Décor
Home & Garden – Patio Furniture
Home & Garden – Pest Control
Home & Garden – Pet Supplies
Home & Garden – Plants & Planting
Home & Garden – Small Appliances
Home & Garden – Tableware
Home & Garden – Vacuums, Cleaning & Storage
Home & Garden – Weather Instruments
Home & Garden – Wine Accessories

Industrial & Scientific – Adhesive Tapes
Industrial & Scientific – Fasteners
Industrial & Scientific – Industrial Abrasives
Industrial & Scientific – Industrial Adhesives
Industrial & Scientific – Industrial Cutting Tools
Industrial & Scientific – Industrial Hoses
Industrial & Scientific – Material Handling Equipment
Industrial & Scientific – Mechanical Components
Industrial & Scientific – Precision Measurement Products
Industrial & Scientific – Raw Materials

Jewelry – Accessories
Jewelry – Body Jewelry
Jewelry – Bracelets
Jewelry – Brooches & Pins
Jewelry – Charms
Jewelry – Children’s Jewelry
Jewelry – Earrings
Jewelry – Engagement
Jewelry – Jewelry Sets
Jewelry – Men’s Jewelry
Jewelry – Necklaces & Pendants
Jewelry – Religious Jewelry
Jewelry – Rings
Jewelry – Wedding & Anniversary

Kindle Store – Advice & How-to
Kindle Store – Arts & Entertainment
Kindle Store – Biographies & Memoirs
Kindle Store – Business & Investing
Kindle Store – Children’s Chapter Books
Kindle Store – Computers & Internet
Kindle Store – Fantasy
Kindle Store – Fiction
Kindle Store – History
Kindle Store – Humor
Kindle Store – Lifestyle & Home
Kindle Store – Literary Fiction
Kindle Store – Mystery & Thrillers
Kindle Store – Nonfiction
Kindle Store – Parenting & Families
Kindle Store – Politics & Current Events
Kindle Store – Reference
Kindle Store – Religion & Spirituality
Kindle Store – Romance
Kindle Store – Science
Kindle Store – Science Fiction
Kindle Store – Sports
Kindle Store – Travel

Miscellaneous – Aircraft
Miscellaneous – Antiques
Miscellaneous – Coins
Miscellaneous – Collectibles
Miscellaneous – General
Miscellaneous – Other Products
Miscellaneous – Scientific Supplies
Miscellaneous – Stamps
Miscellaneous – Vehicles
Miscellaneous – Watercraft

MP3 Downloads – MP3 Albums
MP3 Downloads – MP3 Songs

Music – Alternative Rock
Music – Blues
Music – Broadway & Vocalists
Music – Children’s Music
Music – Christian & Gospel
Music – Classic Rock
Music – Classical
Music – Country
Music – Dance & DJ
Music – Folk
Music – Hard Rock & Metal
Music – International
Music – Jazz
Music – Latin Music
Music – Miscellaneous
Music – New Age
Music – Pop
Music – R&B
Music – Rap & Hip-Hop
Music – Rock
Music – Soundtracks

Musical Instruments – Band & Orchestra
Musical Instruments – Bass Guitars
Musical Instruments – DJ, Electronic Music & Karaoke
Musical Instruments – Drums & Percussion
Musical Instruments – Folk & World Instruments
Musical Instruments – Guitars
Musical Instruments – Instrument Accessories
Musical Instruments – Keyboards
Musical Instruments – Live Sound & Stage
Musical Instruments – Recording Equipment

Office Products – Business Presentation Supplies
Office Products – Educational Supplies
Office Products – Mailroom Supplies
Office Products – Office Furniture & Accessories
Office Products – Office Lighting
Office Products – Office Supplies

Cell Phones & Service – All
Cell Phones & Service – Bluetooth
Cell Phones & Service – Camera
Cell Phones & Service – E-mail
Cell Phones & Service – Flip
Cell Phones & Service – Kid’s
Cell Phones & Service – Mobile Broadband Cards
Cell Phones & Service – MP3
Cell Phones & Service – PDA
Cell Phones & Service – Prepaid
Cell Phones & Service – Video
Cell Phones & Service – Wi-Fi

Software – Business & Office
Software – Children’s Software
Software – Education & Reference
Software – Graphics
Software – Home & Hobbies
Software – Language & Travel
Software – Linux
Software – Macintosh
Software – Networking
Software – Operating Systems
Software – Outlet
Software – Personal Finance
Software – Programming
Software – Software for Handhelds
Software – Utilities
Software – Video & Music
Software – Web Development

Toys & Games – Action Figures
Toys & Games – Activities & Amusements
Toys & Games – Arts & Crafts
Toys & Games – Bikes, Skates & Ride-Ons
Toys & Games – Construction, Blocks & Models
Toys & Games – Dolls
Toys & Games – Electronics for Kids
Toys & Games – Games
Toys & Games – Hobbies
Toys & Games – Kids’ Furniture & Room Décor
Toys & Games – Learning & Education
Toys & Games – Music
Toys & Games – Party Supplies
Toys & Games – Play Vehicles
Toys & Games – Preschool
Toys & Games – Pretend Play & Dress-up
Toys & Games – Puzzles
Toys & Games – Sports & Outdoor Play
Toys & Games – Stuffed Animals & Plush
Toys & Games – Toy Figures & Playsets

Unbox Video Downloads – International
Unbox Video Downloads – Movies
Unbox Video Downloads – Nonfiction
Unbox Video Downloads – TV

DVD – Action & Adventure
DVD – African American Cinema
DVD – Animation
DVD – Anime & Manga
DVD – Art House & International
DVD – Classics
DVD – Comedy
DVD – Cult Movies
DVD – Documentary
DVD – Drama
DVD – Educational
DVD – Fitness & Yoga
DVD – Gay & Lesbian
DVD – Horror
DVD – Kids & Family
DVD – Military & War
DVD – Music Video & Concerts
DVD – Musicals & Performing Arts
DVD – Mystery & Suspense
DVD – Science Fiction & Fantasy
DVD – Special Interests
DVD – Sports
DVD – Television
DVD – Westerns

"Computer & Video Games – ":http://astore.amazon.com/vgamesbuy-20

VHS – Action & Adventure
VHS – African American Cinema
VHS – Animation
VHS – Anime & Manga
VHS – Art House & International
VHS – Boxed Sets
VHS – Classics
VHS – Comedy
VHS – Cult Movies
VHS – Documentary
VHS – Drama
VHS – Educational
VHS – Fitness
VHS – Gay & Lesbian
VHS – Horror
VHS – Kids & Family
VHS – Military & War
VHS – Music Video & Concerts
VHS – Musicals & Performing Arts
VHS – Mystery & Suspense
VHS – Science Fiction & Fantasy
VHS – Spanish Language
VHS – Special Interests
VHS – Sports
VHS – Television
VHS – Westerns
VHS – Widescreen

Watches – Accessories
Watches – Casual Watches
Watches – Collectible Watches
Watches – Dress Watches
Watches – Fashion Watches
Watches – Pocket Watches
Watches – Sport Watches

Self-referential many-to-many relationship

if you are interested in using has_many :through instead of has_and_belongs_to_many see

Josh_Susser_blog

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 %>

-Peter Michaux

A slight alternative

There was some discussion on this subject on the SitePoint forums.

The original thread can be seen here

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.

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

-Luke Redpath

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

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

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

Bath and Shower
Fragrance
Gift Sets
Hair Care
Makeup
Men’s Grooming
Shaving and Hair Removal
Skin Care
Tools and Accessories