13
votes

I'm having a hard time understanding how to implement a single model self-join in Rails. The Guide to ActiveRecord Associations section 2.10 briefly explains Self-Joins but doesn't offer enough information, and every example or post about this such references the Friendly Friend Railcast example that isn't a single model self join, as described in the Rails Guide section 2.10.

The idea is a model that has_many and belongs_to itself, without needing a separate table for the relationship. The only reason I see for needing a separate table is if you want the relationship to contain more information than just the relationship. e.g. "best friends", "barely know them"

I have a simple Post schema:

create_table "posts", :force => true do |t|
    t.datetime "posted"
    t.string   "nick"
    t.string   "title"
    t.text     "content"
    t.integer  "parent_post_id"
    t.datetime "created_at",     :null => false
    t.datetime "updated_at",     :null => false
end

The parent_post_id is a self-reference to other Post post_id's. The posts.rb model has the relationship defined:

class Post < ActiveRecord::Base
  has_many :replies, :class_name => "Post"
  belongs_to :parent_post, :class_name => "Post",
    :foreign_key => "parent_post_id"
end

In the Controller or View I'm hoping to be able to do something like this:

@posts.each do |post|
  ...
  @replies = post.replies
  @replies.each do |reply|
    ...
  end
end

Or find a post's parent:

@parent_post = post.parent_post

This may all be some syntax mis-understanding. So thanks in advance to anyone who can slap some sense into me. I've looked through every SO and blog post out there and none try the single model self-referential self-join method described in the Guide.

Points for anyone offering an explanation that doesn't point to the Friendly Friend example that uses a separate relationship table.

1
It'd be easier to get started if you described what was/wasn't happening the way you expected. Also, things like act_as_tree are single-table, single-model self-referential--have you considered looking at something like that?Dave Newton
I thought I did. I was expecting to be able to call post.replies and get a set of Post instances that were populated based on the parent post, by it's parent_post_id = post_id.dubmojo
That explains what you did. Not what happened.Dave Newton
Sorry about that. post.replies chucked an SQL error. See my answer below. The error was a SQL error that clued me in eventually to what the problem was. The post.replies was generating SQL where the :replies field key was defaulted to post_id. Not parent_post_id as I wanted. Once I added :foreign_key to :replies, now post.replies will query for posts where the parent_post_id is used.dubmojo

1 Answers

26
votes

I was missing the has_many foreign key to "parent_post_id". Once its set, the post.replies will reference other Post instances by parent_post_id.

The posts.rb model has the relationship defined:

class Post < ActiveRecord::Base
  has_many :replies, :class_name => "Post",
    :foreign_key => "parent_post_id"
  belongs_to :parent_post, :class_name => "Post",
    :foreign_key => "parent_post_id"
end

I can now create Posts, assign a parent_post_id to a different Post, then later get all Posts that are replies to any parent Post.