# Active Record - find - select option

**URL:** <https://www.ruby-forum.com/t/active-record-find-select-option/155463>\
**Category:** Rails\
**Created:** [December 12, 2008, 3:08am UTC](https://www.ruby-forum.com/t/active-record-find-select-option/155463 "2008-12-12T03:08:06Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![bruparel](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e47774/32.png) [@bruparel](https://www.ruby-forum.com/u/bruparel)\
**Post date:** [December 12, 2008, 3:08am UTC](https://www.ruby-forum.com/t/active-record-find-select-option/155463/1 "2008-12-12T03:08:06Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![bruparel](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e47774/32.png) [@bruparel](https://www.ruby-forum.com/u/bruparel)\
**Post date:** [December 12, 2008, 4:00am UTC](https://www.ruby-forum.com/t/active-record-find-select-option/155463/2 "2008-12-12T04:00:41Z")

</div>

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](http://railswheels.com)

---

<div class="post-metadata">

**Author:** ![bruparel](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e47774/32.png) [@bruparel](https://www.ruby-forum.com/u/bruparel)\
**Post date:** [December 12, 2008, 4:47am UTC](https://www.ruby-forum.com/t/active-record-find-select-option/155463/3 "2008-12-12T04:47:25Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![bruparel](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e47774/32.png) [@bruparel](https://www.ruby-forum.com/u/bruparel)\
**Post date:** [December 12, 2008, 5:46am UTC](https://www.ruby-forum.com/t/active-record-find-select-option/155463/4 "2008-12-12T05:46:40Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![bruparel](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e47774/32.png) [@bruparel](https://www.ruby-forum.com/u/bruparel)\
**Post date:** [December 12, 2008, 8:49am UTC](https://www.ruby-forum.com/t/active-record-find-select-option/155463/5 "2008-12-12T08:49:10Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![bruparel](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e47774/32.png) [@bruparel](https://www.ruby-forum.com/u/bruparel)\
**Post date:** [December 12, 2008, 4:54am UTC](https://www.ruby-forum.com/t/active-record-find-select-option/155463/6 "2008-12-12T04:54:08Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![bruparel](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e47774/32.png) [@bruparel](https://www.ruby-forum.com/u/bruparel)\
**Post date:** [December 12, 2008, 8:50am UTC](https://www.ruby-forum.com/t/active-record-find-select-option/155463/7 "2008-12-12T08:50:57Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![bruparel](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e47774/32.png) [@bruparel](https://www.ruby-forum.com/u/bruparel)\
**Post date:** [December 12, 2008, 9:01am UTC](https://www.ruby-forum.com/t/active-record-find-select-option/155463/8 "2008-12-12T09:01:44Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![bruparel](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/e47774/32.png) [@bruparel](https://www.ruby-forum.com/u/bruparel)\
**Post date:** [December 13, 2008, 10:02pm UTC](https://www.ruby-forum.com/t/active-record-find-select-option/155463/9 "2008-12-13T22:02:11Z")

</div>

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