Rails Self referencing using non-primary key

Question

Please help.

I have a self referencing model and want the foreign key to refer to a non-primary key that is scoped.

In the list table, I have a sequence_id column which contains the incremental id of the list that is only unique in a listset. Meaning the same sequence_id can be seen for list of another listset. However, as this sequence_id is not unique throughout the table, I didn't make it a primary key.

How do I achieve the self-referencing relation using this non-primary key?

Thank you very much.

Model:

 Class Listset
       has_many :lists, :dependent => :delete_all
       has_many :items, :through => :lists
    end

    Class List
       belongs_to :listset

       belongs_to :parentList, :class_name => "List"
       has_many :childList, :class_name => "List", foreign_key => "parent"

       has_many :items, :dependent => :delete_all
    end

    Class item
        belongs_to :list
    end

score 0 · Answer 1 · answered Oct 16 '12 at 07:04

Can you clarify what you are asking about the sequence_id?

This should help with your self-referencing relation:

class List < ActiveRecord::Base
  belongs_to :listset
  belongs_to :parent,      :class_name => "List"
  has_many   :child_lists, :class_name => "List", :foreign_key => "parent_id"
  # etc...
end

This assumes you have a parent_id column in the database table.

The convention in Rails is to underscore named relations, i.e. :child_lists instead of :childList.

Using belongs_to :parent indicates to ActiveRecord to look for the parent_id column in the lists table, which should be the foreign key referenced in the has_many relationship.

Thank you for the prompt reply. I'll take note about the naming convention. Thanks for pointing it out. What I'm trying to achieve is for ActiveRecord to look for sequence_id instead of parent_id as the parent column keeps the sequence_id, that is not a primary key, and not the id of the parent which is the primary key. — Mich, Oct 17 '12 at 08:25

score 0 · Answer 2 · answered Oct 16 '12 at 08:12

If I understand you correctly, you already know how to model the self-referencing relation using primary keys. Each List simply has a parent_id that refers to some other Lists's id (which is a primary key). I think what you're asking is whether you can model the self-referencing relation using the non-primary key sequence_id instead of the default primary key id.

Conceptually, this is possible because each record in the List model would be uniquely identified by both its sequence_id and its listset_id, the reason being that sequence_id is unique within the scope of a listset_id.

However, I don't think you can safely manipulation belongs_to to find a List's parent in this way. According to railsguides,

... by declaring that one model belongs_to another, you instruct Rails to maintain Primary Key–Foreign Key information between instances of the two models...

That is, belongs_to expects to find records using a foreign key that is a primary key. While you could attempt to make belongs_to identify the parent based on the listset_id scope and sequence_id with something like

belongs_to :parent_list, foreign_key: :parent_id, class_name: "List", conditions: proc { {:listset_id => listset_id, :sequence_id => parent_id} }

this might lead to unexpected behaviour, as belongs_to still expects to use a primary key to identify the associated model.

Though, I'm not entirely sure if this is what you were going for. Let us know.

Thank you for the prompt reply. Yes that is what I'm trying to achieve. But sadly, nothing happened when I tried to get the children list by calling list.childList — Mich, Oct 17 '12 at 08:22
You still need to define `child_list` properly. The above `belongs_to` will only give you the parent. For the children, try `has_many :child_lists, foreign_key: :parent_id, class_name: "List", conditions: proc { {:listset_id => listset_id} }`. I haven't tried this myself, so I'm not sure that it will work. — cdesrosiers, Oct 17 '12 at 09:25
I tried this [ belongs_to :parentList, :class_name => "List", :conditions => proc { {:listset_id => listset_id, :parent => sequence_id} } ] and [ has_many :childList, :class_name => "List", foreign_key => "parent", :conditions => proc { {:listset_id => listset_id, :sequence_id=> parent } } } ] but nothing happen when I tried to get the children list by calling list.childList — Mich, Oct 19 '12 at 02:32

Rails Self referencing using non-primary key

2 Answers2