Rails - Sep 2007 - ActiveRecord calculations with joins on renamed foreign keys?

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


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

rbibby wrote:
> 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?
Show us your schema/migrations.


-- 
Michael Wang

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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

On Sep 7, 6:01 pm, Michael Wang
<rails-u...-JtyympAsP2K7zZZRDBGcUA@public.gmane.org>
wrote:
> rbibby wrote:
> > 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?
>
> Show us your schema/migrations.
>
> --
> Michael Wang

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

What happens when you do this?:

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


-- 
Michael Wang

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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


On Sep 7, 6:26 pm, Michael Wang
<rails-u...-JtyympAsP2K7zZZRDBGcUA@public.gmane.org>
wrote:
> What happens when you do this?:
>
> @users = User.sum(''book.score'', :include =>
''books'')
>
> --
> Michael Wang

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Try it like this:

User Model:
has_many :books, :foreign_key => "created_by"


-- 
Michael Wang

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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

On Sep 7, 8:07 pm, Michael Wang
<rails-u...-JtyympAsP2K7zZZRDBGcUA@public.gmane.org>
wrote:
> Try it like this:
>
> User Model:
> has_many :books, :foreign_key => "created_by"
>
> --
> Michael Wang

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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 Wang

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---