Forum: Ruby on Rails Active Record - find - select option

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Bharat R. (Guest)
on 2008-12-12 04:08
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
Mark Reginald J. (Guest)
on 2008-12-12 05:00
(Received via mailing list)
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
Bharat R. (Guest)
on 2008-12-12 05:47
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
Bharat R. (Guest)
on 2008-12-12 05:54
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?
Priya B. (Guest)
on 2008-12-12 06:46
@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.
Frederick C. (Guest)
on 2008-12-12 09:49
(Received via mailing list)
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
Frederick C. (Guest)
on 2008-12-12 09:50
(Received via mailing list)
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
Priya B. (Guest)
on 2008-12-12 10:01
Have you tried the query which i have send you?
It'll work..
Bharat R. (Guest)
on 2008-12-13 23:02
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
This topic is locked and can not be replied to.