Active Record - find - select option

I have an accounts table and a coaches table. The tables are as follows
(with unimportant fields deleted):

Accounts (id, name,…)
Coaches (id, account_id, business_id, account_limit)

Each account can either have one (or none) coach at any point in time.

I have modeled these relationships in corresponding ActiveRecord classes
as follows:

class Account < ActiveRecord::Base
has_one :coach
end

class Coach < ActiveRecord::Base
belongs_to :account
end

I am trying to use will_paginate for paging through the list of coaches
and look-up their names from the accounts table (and some more fields).
To get to that, I am first trying to create a corresponding find query
in ActiveRecord. Here is my query:

Coach.find :all, :select => ‘c.id, a.id, a.name’,
:joins => ‘as c inner join accounts as a on a.id =
c.account_id’,
:order => ‘a.name’

It is only returning the following in irb:

[#<Coach id: 99>]

That is, it fails to return the account.id (or a.id) and account.name
(or a.name)

How do I resolve this?

Thanks in advance for your help and time.

Bharat

Bharat R. wrote:

(or a.name)
Given that you’re not using :include => :account for efficiency reasons,
you have to ensure that the account id does not override the coach id:

Coach.find :all, :joins => :account, :order => ‘accounts.name’,
:select => ‘coaches.id, accounts.id as account_id, accounts.name’


Rails Wheels - Find Plugins, List & Sell Plugins -
http://railswheels.com

Mark,
Thanks for your response. I tried that and the ids are indeed being
returned by not the accounts.name. I am not sure why.
Bharat

@account = Account.paginate(:all, :page => params[:page],:order=>‘name’,
:per_page => 10, :joins=>“as a inner join coaches as c on
a.id=c.account_id”, :select => “a.id, a.name, c.id”)

Try this query. Its using will_paginate only. In select option you can
put whatever fields you want in those 2 tables.

On 12 Dec 2008, at 02:08, Bharat R. wrote:

That is, it fails to return the account.id (or a.id) and account.name
(or a.name)

How do I resolve this?
alias the account id to be something other than id.

Fred

I should mention something else. I am on Rails 2.1.0 and even
find_by_sql does not return columns from multiple tables as the Rails
API documentation says:

This example is from Rails API docs:

A simple SQL query spanning multiple tables

Post.find_by_sql “SELECT p.title, c.author FROM posts p, comments c
WHERE p.id = c.post_id”

[#<Post:0x36bff9c @attributes={“title”=>“Ruby Meetup”,
“first_name”=>“Quentin”}>, …]

I followed this for my queries and I can only get columns from either of
the tables but not both.

Is this a bug?

On 12 Dec 2008, at 03:54, Bharat R. wrote:

[#<Post:0x36bff9c @attributes={“title”=>“Ruby Meetup”,
“first_name”=>“Quentin”}>, …]

I followed this for my queries and I can only get columns from
either of
the tables but not both.

Is this a bug?
The attributes are there, but the default inspect method on
ActiveRecord objects (which is used to display objects in the console
doesn’t display them

Fred

Have you tried the query which i have send you?
It’ll work…

Hello Mark, Priya, and Fred,
I sincerely appreciate you all taking trouble to share your knowledge
with me. I had to be away from development for a day and half to attend
to other urgent matters. I am back on the job and going through your
feedback. I will post the results as soon as I have them.
Regards to you all.
Bharat