Self referential HABTM (has_and_belongs_to_many) in Rails 3

create_related_users.rb

class CreateRelatedUsers < ActiveRecord::Migration
  def change
    create_table :related_users, id: false do |t|
      t.integer :user_id
      t.integer :related_user_id
    end
  
    add_index(:related_users, [:user_id, :related_user_id], :unique => true)
    add_index(:related_users, [:related_user_id, :user_id], :unique => true)
  end
end

create_users.rb

class CreateUsers < ActiveRecord::Migration
  def change
    create_table :users do |t|
      t.timestamps
    end
  end
end

user.rb

class User < ActiveRecord::Base
  # attr_accessible :title, :body

  # HABTM Self referential relationship seems to be working with custom finder_sql
  has_and_belongs_to_many :related_users,
                          class_name: 'User',
                          join_table: :related_users,
                          foreign_key: :user_id,
                          association_foreign_key: :related_user_id,
                          uniq: true,
                          finder_sql: proc { %(SELECT DISTINCT "users".* FROM "users"
                                              INNER JOIN "related_users" ON "users"."id" = "related_users"."related_user_id"
                                              WHERE "related_users"."user_id" =  #{self.id}
                                              UNION
                                              SELECT DISTINCT "users".* FROM "users"
                                              INNER JOIN "related_users" ON "users"."id" = "related_users"."user_id"
                                              WHERE "related_users"."related_user_id" =  #{self.id} )}
end

TODO: It's possible to add the same associations multiple times, some custom :insert_sql should be added to fix this.

add_index should be within the body of change method, isn't it? :)

@andriytyurnikov I think you're right, I'll update the gist. Cheers!

if a I call user.related_users.limit(2), activerecord ignores my finder_sql SQL statement. Its a bug? Rails 3.2