ActiveRecord calculations with joins on renamed foreign keys

Rails
1

Hi,

Could someone please explain to me how to do use the ActiveRecord
helpers to do calculations with joins on renamed foreign keys?

My situation:

User Model:
has_many :books

Book Model:
belongs_to :created_by,
:class_name => ‘User’,
:foreign_key => ‘created_by’

I’m trying to calculate a list of the users by summed book scores,
with the following code.

@users = User.sum(‘books.score’, :include => ‘books’)

I’m getting the error: 42S22Unknown column ‘books.user_id’ in ‘on
clause’. Isn’t Rails supposed to be smart enough to realize that the
foreign key has been renamed? How do I tell it to use created_by as
the foreign key here?

Thanks,
R

2

rbibby wrote:

Book Model:
I’m getting the error: 42S22Unknown column ‘books.user_id’ in ‘on
clause’. Isn’t Rails supposed to be smart enough to realize that the
foreign key has been renamed? How do I tell it to use created_by as
the foreign key here?

Show us your schema/migrations.

–
Michael W.

3

Here you go…

class CreateUsers < ActiveRecord::Migration
def self.up
create_table “users”, :force => true do |t|
t.column :login, :string
t.column :email, :string
t.column :crypted_password, :string, :limit => 40
t.column :salt, :string, :limit => 40
t.column :created_at, :datetime
t.column :updated_at, :datetime
t.column :remember_token, :string
t.column :remember_token_expires_at, :datetime
t.column :activation_code, :string, :limit => 40
t.column :activated_at, :datetime
t.column :admin_flag, :integer
end
end

end

class CreateBooks < ActiveRecord::Migration

def self.up
create_table :books do |t|
t.column :title, :string
t.column :description, :text
t.column :score, :integer, :default => 0
t.column :created_at, :timestamp
t.column :created_by, :integer

end

end

end

4

What happens when you do this?:

@users = User.sum(‘book.score’, :include => ‘books’)

–
Michael W.

5

Try it like this:

User Model:
has_many :books, :foreign_key => “created_by”

–
Michael W.

6

It works! Thank you so much! So is the key that the foreign key
needs to be specified on both sides of the association? Is the books
model being done correctly as is?

Thanks again,
R

7

Mysql::Error: #42S02Unknown table ‘book’ in field list: SELECT
sum(book.score) AS sum_book_score FROM users LEFT OUTER JOIN books ON
books.user_id = users.id ORDER BY score desc

8

rbibby wrote:

It works! Thank you so much! So is the key that the foreign key
needs to be specified on both sides of the association? Is the books
model being done correctly as is?

Thanks again,
R

Yeah apparently it does, though the examples in AWDR don’t do it that
way for some reason.

For the Book model you can try:

book = Book.find(1)
book.user

and it should give you back the user for that book.

–
Michael W.