Active Record - find - select option

Rails
1

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

2

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

3

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

4

@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.

5

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

6

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?

7

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

8

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

9

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