Ruby on Rails
HowToUseManyToManyAgainstASingleTable

see also HowToCreateASelfReferentialManyToManyRelationship

Let’s say you need to model a node graph, something like this:

So you create a database like the following:

create_table "nodes" do |t|    
  t.column :name,  :string
end 
    
create_table "nodelinks", :id => false do |t|
  t.column :source_id,      :integer, :null => false
  t.column :destination_id, :integer, :null => false
end

A node (source) can be connected to any other node (destinations).

Depending on directionality and how you store it in the database, you can simply do

<pre> has_and_belongs_to_many :nodes, :join_table => 'nodelink', :association_foreign_key => 'destination_id', :foreign_key => 'source_id'

This assumes that nodelink contains both a row with “Node B connects to Node C” and one with “Node C connects to Node B” in order to model the seemingly non-directional graph.

— Jakob S

To create a directional version using the above tables:

class Node < ActiveRecord::Base
  has_and_belongs_to_many :linked_from,
    :class_name => 'Node',
    :join_table => 'nodelink',
    :association_foreign_key => 'source_id',
    :foreign_key => 'destination_id'

has_and_belongs_to_many :linked_to,
:class_name => ‘Node’,
:join_table => ‘nodelink’,
:association_foreign_key => ‘destination_id’,
:foreign_key => ‘source_id’
end

Note that the ordering of source_id versus destination_id is what makes the difference between the incoming links versus the outgoing links.

— Phrogz

One might argue also that this is contrived, and you should create a real Edge class. This way when you finally realise “whoops, I need to colour the edges of the graph”, you have somewhere to store that property.

— AC

How would you go about converting the edges into a full blown model with edge colours and weights?