Rails ORM, can it cope with a compound Primary key of two foreign keys referencing the same table?

Edited 30th, 03:16, to show improved migration and model

I have a table of elements that can each be connected to many other elements. Is it possible to do this in Rails' migrations and models? (the following is it implemented in SQL)

Table of Elements: has an integer id开发者_开发百科 primary key field, along with others.

Table of Inter Element Links:

CREATE TABLE inter_element_links
(
  element1_id integer NOT NULL,
  element2_id integer NOT NULL,
  CONSTRAINT inter_element_links_pkey PRIMARY KEY (element1_id, element2_id),
  CONSTRAINT foreign_key_on_id1 FOREIGN KEY (element1_id)
      REFERENCES elements (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT foreign_key_on_id2 FOREIGN KEY (element2_id)
      REFERENCES elements (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

And this query returns all elements linked to a particular element by its id, e.g. 3:

SELECT *
FROM elements
INNER JOIN inter_element_links ON (elements.id = inter_element_links.element1_id
  OR elements.id = inter_element_links.element2_id)
WHERE (inter_element_links.element1_id = 3 OR inter_element_links.element2_id = 3)
AND elements.id <> 3

This works in Postgres and I'd like to translate into Rails but am struggling to find a solution for Rails... is what I'm doing stupid or is it just not that common?

So far I have the migration (which has a fake compound primary key and no foreign keys referencing elements.id):

class CreateInterElementLinks < ActiveRecord::Migration
  def self.up
    create_table :inter_element_links, :id => false do |t|
      t.integer :element1_id, :null => false
      t.integer :element2_id, :null => false
    end
    add_index :inter_element_links, [:element1_id, :element2_id], :unique => true
  end

  def self.down
    remove_index :inter_element_links, [:element1_id, :element2_id]
    drop_table :inter_element_links
  end
end

And have the following models:

class Element < ActiveRecord::Base
  belongs_to :user
  has_many :inter_element_links1, :foreign_key => 'element1_id', :class_name => 'InterElementLink', :dependent => :destroy
  has_many :inter_element_links2, :foreign_key => 'element2_id', :class_name => 'InterElementLink', :dependent => :destroy
  has_many :elements1, :through => :inter_element_links1
  has_many :elements2, :through => :inter_element_links2

  def inter_element_links  ## Unless there's a more elegant rails solution, I will try to extend ActiveRecord::HasManyThroughAssociation, so things like:  an_element.destroy, will destroy the entry in the inter_element_links table, and calls to an_element.elements will work.
    inter_element_links1 + inter_element_links2
  end

  def elements
    elements1 + elements2
  end
end

class InterElementLink < ActiveRecord::Base
  set_primary_keys :element1_id, :element2_id   ## I have included the 'composite_primary_keys', '=3.1.0' gem
  belongs_to :element
end

Any thoughts would be much appreciated!

---

I've found a few things over the last 24 hours that seem to be close but haven't got me all the way yet:

• Similar problem was discussed here but I haven't been able to track down the proposed plugin and it doesn't help for the migrations side of things.
• I have used this plugin to successfully get a Rails imitation Primary key on InterElementLink so if there's one where element1_id = 3 and element2_id = 5, a call to InterElementLink.find(3,5) works, but obviously InterElementLink.find(5,3) doesn't work... and I/ActiveRecord will need it to.
• I understand ORMs including ActiveRecord don't support foreign keys, see a good discussion about this (though I understand you can use this plug in). I tried this to make a primary key, but that failed.

Thanks very much!