# ActiveRecord calculations with joins on renamed foreign keys

**URL:** <https://www.ruby-forum.com/t/activerecord-calculations-with-joins-on-renamed-foreign-keys/114117>\
**Category:** Rails\
**Created:** [September 8, 2007, 2:46am UTC](https://www.ruby-forum.com/t/activerecord-calculations-with-joins-on-renamed-foreign-keys/114117 "2007-09-08T02:46:08Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![rbibby](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/74df32/32.png) [@rbibby](https://www.ruby-forum.com/u/rbibby)\
**Post date:** [September 8, 2007, 2:46am UTC](https://www.ruby-forum.com/t/activerecord-calculations-with-joins-on-renamed-foreign-keys/114117/1 "2007-09-08T02:46:08Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![rbibby](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/74df32/32.png) [@rbibby](https://www.ruby-forum.com/u/rbibby)\
**Post date:** [September 8, 2007, 3:02am UTC](https://www.ruby-forum.com/t/activerecord-calculations-with-joins-on-renamed-foreign-keys/114117/2 "2007-09-08T03:02:09Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![rbibby](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/74df32/32.png) [@rbibby](https://www.ruby-forum.com/u/rbibby)\
**Post date:** [September 8, 2007, 3:17am UTC](https://www.ruby-forum.com/t/activerecord-calculations-with-joins-on-renamed-foreign-keys/114117/3 "2007-09-08T03:17:10Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![rbibby](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/74df32/32.png) [@rbibby](https://www.ruby-forum.com/u/rbibby)\
**Post date:** [September 8, 2007, 3:27am UTC](https://www.ruby-forum.com/t/activerecord-calculations-with-joins-on-renamed-foreign-keys/114117/4 "2007-09-08T03:27:10Z")

</div>

What happens when you do this?:

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

–  
Michael W.

---

<div class="post-metadata">

**Author:** ![rbibby](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/74df32/32.png) [@rbibby](https://www.ruby-forum.com/u/rbibby)\
**Post date:** [September 8, 2007, 5:09am UTC](https://www.ruby-forum.com/t/activerecord-calculations-with-joins-on-renamed-foreign-keys/114117/5 "2007-09-08T05:09:20Z")

</div>

Try it like this:

User Model:  
has\_many :books, :foreign\_key =\> “created\_by”

–  
Michael W.

---

<div class="post-metadata">

**Author:** ![rbibby](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/74df32/32.png) [@rbibby](https://www.ruby-forum.com/u/rbibby)\
**Post date:** [September 8, 2007, 9:25am UTC](https://www.ruby-forum.com/t/activerecord-calculations-with-joins-on-renamed-foreign-keys/114117/6 "2007-09-08T09:25:32Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![rbibby](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/74df32/32.png) [@rbibby](https://www.ruby-forum.com/u/rbibby)\
**Post date:** [September 8, 2007, 4:12am UTC](https://www.ruby-forum.com/t/activerecord-calculations-with-joins-on-renamed-foreign-keys/114117/7 "2007-09-08T04:12:17Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![rbibby](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/74df32/32.png) [@rbibby](https://www.ruby-forum.com/u/rbibby)\
**Post date:** [September 8, 2007, 11:58am UTC](https://www.ruby-forum.com/t/activerecord-calculations-with-joins-on-renamed-foreign-keys/114117/8 "2007-09-08T11:58:44Z")

</div>

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.