How can I setup a has_many associated to look in 2 columns in a db table (category_a_id and category_b_id)

Question

I need to be able to link up categories with each other.

I have a category_links table with 2 columns category_a_id and category_b_id to hold the id of the 2 categories that are linked.

I want to be able to call Category.find(1).category_links and it will grab all records with the id 1 from both the category_a_id and category_b_id columns. Also if a category is destroyed it will delete any associated records in the category_link db if its associated in the category_a_id or category_b_id column.

currently in the category_links model I have

belongs_to :category_b, :class_name => :Category
belongs_to :category_a, :class_name => :Category

and in the category db

has_many(:category_links, :foreign_key => :category_a_id, :dependent => :destroy)
has_many(:category_links, :foreign_key => :category_b_id, :dependent => :destroy)

But this only assosiates records from the category_b_id column with the matching id not from both. So calling Category.find(1).category_links doesnt grab records matching from both category_a_id and category_b_id. Same goes for destroying a category.

How can I get it to work so if I destroy a category it looks into both columns for the associated category and calling Category.find(1).category_links also looks in both columns for a matching record?

Answer 1

There's no simple way to achieve that through has_many, but if you don't have to be fancy just create a custom method on Category:

def category_links
  @category_links ||= CategoryLinks.where('category_a_id = ? or category_b_id = ?', id, id)
end