Simple SQL join in Rails

Question

I have a users table and a pools table. The users table has a column called facebook_id which I want to set 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?

The foreign_key should be the same value in both association mappings (creator_facebook_id). Getting ActiveRecord to use facebook_id as the primary key of the User class is a different issue. — Rafe
– Rafe, Commented Jun 22, 2011 at 16:39

coreyward · Accepted Answer · 2011-06-22 16:12:14Z

2

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.

answered Jun 22, 2011 at 16:12

coreyward

80.5k21 gold badges147 silver badges176 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

pcg79 · Accepted Answer · 2011-06-22 16:04:58Z

0

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

answered Jun 22, 2011 at 16:04

pcg79

1,2839 silver badges20 bronze badges

1 Comment

coreyward Over a year ago

These are the default values for the class names, so this won't help.

firien · Accepted Answer · 2011-06-22 17:00:39Z

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

Collectives™ on Stack Overflow

Simple SQL join in Rails

3 Answers 3

Comments

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Related