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
--
Posted via http://www.ruby-forum.com/.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
Bharat Ruparel wrote:> 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)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 --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
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 -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
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?
--
Posted via http://www.ruby-forum.com/.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
@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. -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
On 12 Dec 2008, at 02:08, Bharat Ruparel wrote:> y: > > 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?alias the account id to be something other than id. Fred> > > Thanks in advance for your help and time. > > Bharat > -- > Posted via http://www.ruby-forum.com/. > > >--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
On 12 Dec 2008, at 03:54, Bharat Ruparel wrote:> > 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?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> > -- > Posted via http://www.ruby-forum.com/. > > >--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Have you tried the query which i have send you? It''ll work.. -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
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 -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---