Simple SQL join in Rails

I have a users table and a pools table. The users table has a column called facebook_id which I want to set 开发者_Go百科to be the primary_key. The pools table has a column called creator_facebook_id.

I want to perform a simple join where users.facebook_id = pools.creator_facebook_id

I'm trying to do this through active record by placing the following code in my models

class Pool < ActiveRecord::Base
  belongs_to :user, :foreign_key => "facebook_id"
end

class User < ActiveRecord::Base
  has_many :pools, :foreign_key => "creator_facebook_id"
end

Then I when I have a list of pools I'd like to be able to do something like this

pool.user.name

And return the name (stored in the users table) associated with the pool. This must be quite simple where am I going wrong?

If you actually have set facebook_id as the primary key on the Users table, you will need to inform Rails of this. There is a :primary_key option available for associations, so you could use this on your Pool model:

belongs_to :user, :primary_key => 'facebook_id', :foreign_key => 'creator_facebook_id'

That should allow you to use Pool.user. Unfortunately, getting ActiveRecord to play nicely with non-standard primary keys is another issue entirely. I suggest you just let it do it's thing with the id column being the primary key and add an index on your facebook_id column.

I believe you're missing the class name.

class Pool < ActiveRecord::Base
  belongs_to :user, :class_name => 'User', :foreign_key => "facebook_id"
end

class User < ActiveRecord::Base
  has_many :pools, :class_name => 'Pool', :foreign_key => "creator_facebook_id"
end

migrations:

class CreateUsers < ActiveRecord::Migration
  def self.up
    create_table :users, :primary_key => 'facebook_id' do |t|
      t.string :name
    end
  end

  def self.down
    drop_table :users
  end
end

class CreatePools < ActiveRecord::Migration
  def self.up
    create_table :pools do |t|
      t.integer :creator_facebook_id
      t.string :name
    end
  end

  def self.down
    drop_table :pools
  end
end

models:

class User < ActiveRecord::Base
  set_primary_key 'facebook_id'
  has_many :pools, :foreign_key => "creator_facebook_id"
end

class Pool < ActiveRecord::Base
  belongs_to :user, :foreign_key => "creator_facebook_id"
end

this will allow for:

pool.user.name

tested with sqlite and rails 3.0.7