Databases
StackOverflow2013
Postsdbo
PORails ORM, can it cope with a compound Primary key of two foreign keys referencing the same table?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PORails ORM, can it cope with a compound Primary key of two foreign keys referencing the same table?
    primarykey
    data
    text
    <p><em>Edited 30th, 03:16, to show improved migration and model</em></p> <p>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)</p> <p><em>Table of Elements:</em> has an integer id primary key field, along with others.</p> <p><em>Table of Inter Element Links:</em></p> <pre><code>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 ) </code></pre> <p>And this query returns all elements linked to a particular element by its id, e.g. 3:</p> <pre><code>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 &lt;&gt; 3 </code></pre> <p><strong>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?</strong></p> <p>So far I have the migration (<a href="https://stackoverflow.com/questions/5226321/fake-a-composite-primary-key-rails">which has a fake compound primary key</a> and no foreign keys referencing elements.id):</p> <pre><code>class CreateInterElementLinks &lt; ActiveRecord::Migration def self.up create_table :inter_element_links, :id =&gt; false do |t| t.integer :element1_id, :null =&gt; false t.integer :element2_id, :null =&gt; false end add_index :inter_element_links, [:element1_id, :element2_id], :unique =&gt; true end def self.down remove_index :inter_element_links, [:element1_id, :element2_id] drop_table :inter_element_links end end </code></pre> <p>And have the following models:</p> <pre><code>class Element &lt; ActiveRecord::Base belongs_to :user has_many :inter_element_links1, :foreign_key =&gt; 'element1_id', :class_name =&gt; 'InterElementLink', :dependent =&gt; :destroy has_many :inter_element_links2, :foreign_key =&gt; 'element2_id', :class_name =&gt; 'InterElementLink', :dependent =&gt; :destroy has_many :elements1, :through =&gt; :inter_element_links1 has_many :elements2, :through =&gt; :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 &lt; ActiveRecord::Base set_primary_keys :element1_id, :element2_id ## I have included the 'composite_primary_keys', '=3.1.0' gem belongs_to :element end </code></pre> <p>Any thoughts would be much appreciated!</p> <hr> <p>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:</p> <ul> <li>Similar problem was <a href="https://groups.google.com/group/rubyonrails-talk/browse_thread/thread/d049c215aac44135/20339bd701bb7806?hl=en&amp;lnk=gst&amp;q=has_two&amp;pli=1#" rel="nofollow noreferrer">discussed here</a> but I haven't been able to track down the proposed plugin and it doesn't help for the migrations side of things.</li> <li>I have <a href="http://compositekeys.rubyforge.org/" rel="nofollow noreferrer">used this plugin</a> 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.</li> <li>I understand ORMs including ActiveRecord don't support foreign keys, <a href="https://stackoverflow.com/questions/928425/why-do-rails-migrations-define-foreign-keys-in-the-application-but-not-in-the-dat">see a good discussion about this</a> (<a href="https://github.com/matthuhiggins/foreigner" rel="nofollow noreferrer">though I understand you can use this plug in</a>). <a href="http://errtheblog.com/posts/14-composite-migrations" rel="nofollow noreferrer">I tried this to make a primary key</a>, but that failed.</li> </ul> <p>Thanks very much!</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USAJP
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PORails ORM, can it cope with a compound Primary key of two foreign keys referencing the same table?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PORails ORM, can it cope with a compound Primary key of two foreign keys referencing the same table?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PORails ORM, can it cope with a compound Primary key of two foreign keys referencing the same table?
      1. USshalott
      1. VTFavorite
    1. COIt's not entirely clear what your question is. What exactly do you want to know?
      singulars
      1. PORails ORM, can it cope with a compound Primary key of two foreign keys referencing the same table?
      1. USsalt.racer
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload