How do I create a self referential association (self join) in a single class using ActiveRecord in Rails?
- by Daniel Chang
I am trying to create a self join table that represents a list of customers who can refer each other (perhaps to a product or a program). I am trying to limit my model to just one class, "Customer".
The schema is:
create_table "customers", force: true do |t|
t.string "name"
t.integer "referring_customer_id"
t.datetime "created_at"
t.datetime "updated_at"
end
add_index "customers", ["referring_customer_id"], name: "index_customers_on_referring_customer_id"
My model is:
class Customer < ActiveRecord::Base
has_many :referrals, class_name: "Customer", foreign_key: "referring_customer_id", conditions: {:referring_customer_id => :id}
belongs_to :referring_customer, class_name: "Customer", foreign_key: "referring_customer_id"
end
I have no problem accessing a customer's referring_customer:
@customer.referring_customer.name
... returns the name of the customer that referred @customer.
However, I keep getting an empty array when accessing referrals:
@customer.referrals
... returns [].
I ran binding.pry to see what SQL was being run, given a customer who has a "referer" and should have several referrals. This is the SQL being executed.
Customer Load (0.3ms) SELECT "customers".* FROM "customers"
WHERE "customers"."id" = ? ORDER BY "customers"."id"
ASC LIMIT 1 [["id", 2]]
Customer Exists (0.2ms) SELECT 1 AS one FROM "customers"
WHERE "customers"."referring_customer_id" = ? AND
"customers"."referring_customer_id" = 'id' LIMIT 1
[["referring_customer_id", 3]]
I'm a bit lost and am unsure where my problem lies. I don't think my query is correct -- @customer.referrals should return an array of all the referrals, which are the customers who have @customer.id as their referring_customer_id.
