Rails - Nov 2011 - Converting SQL query to Rails query for a non model attribute

I have a model called User. This users table in the back end has more
than 40 records.

I am trying to extract the TOP 30 records , grouping people by the total
number of residents in each area, ordering them in a descending manner.

The **sql query** for the same is:-

    select area, count(area) as total from users group by area order by
total desc limit 30;

When doing this in **Rails**, the query looks something like this:-

    User.select(''area, count(area) as
total'').group(''area'').order(''total
DESC'').limit(30)

This gives me the areas in descending order, but it doesn''t share the
count in terms of number of residents per area.

I got to know about use of "**to_sql**" from the [active record rails
casts][1] by Ryan Bates.


  [1]:
http://railscasts.com/episodes/202-active-record-queries-in-rails-3


When I used the to_sql command in my Rails query, I got:-


    User.select(''area, count(area) as
total'').group(''area'').order(''total
DESC'').limit(30).to_sql



    SELECT  area, count(area) as total FROM `users` GROUP BY area ORDER
BY total DESC LIMIT 30


I got the same sql query as above. But somehow since the **count(area)**
is not a direct attribute of the Users model, I''m unable to print it
using a Rails query.

I''m able to get the count using `User.select(''area,
count(area) as
total'').group(''area'')`,

but this is not giving me the areas in descending order. What it returns
is a hash having the area with the count of number of residents in it.

I''m sure there must be something I''m missing.

Could you please share a work around to this , in case you are aware of
one.

Thanks for your time..

-- 
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On Nov 24, 12:28 pm, "Mohnish J."
<li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>
wrote:
> I have a model called User. This users table in the back end has more
> than 40 records.
>
> I am trying to extract the TOP 30 records , grouping people by the total
> number of residents in each area, ordering them in a descending manner.
>
> The **sql query** for the same is:-
>
>     select area, count(area) as total from users group by area order by
> total desc limit 30;
>
> When doing this in **Rails**, the query looks something like this:-
>
>     User.select(''area, count(area) as
total'').group(''area'').order(''total
> DESC'').limit(30)
>
> This gives me the areas in descending order, but it doesn''t share
the
> count in terms of number of residents per area.
>
> I got to know about use of "**to_sql**" from the [active record
rails
> casts][1] by Ryan Bates.
>
>   [1]:http://railscasts.com/episodes/202-active-record-queries-in-rails-3
>
> When I used the to_sql command in my Rails query, I got:-
>
>     User.select(''area, count(area) as
total'').group(''area'').order(''total
> DESC'').limit(30).to_sql
>
>     SELECT  area, count(area) as total FROM `users` GROUP BY area ORDER
> BY total DESC LIMIT 30
>
> I got the same sql query as above. But somehow since the **count(area)**
> is not a direct attribute of the Users model, I''m unable to print
it
> using a Rails query.
What do you mean by print? If you means that it doesn''t show up in the
output from inspect then that''s normal - inspect only ever shows data
from the actual columns on the table. You can still call total (in
this case) to get that value.

Fred

>
> I''m able to get the count using `User.select(''area,
count(area) as
> total'').group(''area'')`,
>
> but this is not giving me the areas in descending order. What it returns
> is a hash having the area with the count of number of residents in it.
>
> I''m sure there must be something I''m missing.
>
> Could you please share a work around to this , in case you are aware of
> one.
>
> Thanks for your time..
>
> --
> Posted viahttp://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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Frederick Cheung wrote in post #1033538:
> On Nov 24, 12:28pm, "Mohnish J."
<li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote:
>>
>>
>> I got the same sql query as above. But somehow since the
**count(area)**
>> is not a direct attribute of the Users model, I''m unable to
print it
>> using a Rails query.
>
> What do you mean by print? If you means that it doesn''t show up in
the
> output from inspect then that''s normal - inspect only ever shows
data
> from the actual columns on the table. You can still call total (in
> this case) to get that value.
>
Hi Fred...

By print, I basically am trying to get the output on the Rails console 
for a "non model attribute" in particular.. which I''m unable
to do ,
yet..

-- 
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On Nov 25, 3:05 pm, "Mohnish J."
<li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>
wrote:
> Frederick Cheung wrote in post #1033538:
>
> > On Nov 24, 12:28pm, "Mohnish J."
<li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote:
>
> >> I got the same sql query as above. But somehow since the
**count(area)**
> >> is not a direct attribute of the Users model, I''m unable
to print it
> >> using a Rails query.
>
> > What do you mean by print? If you means that it doesn''t show
up in the
> > output from inspect then that''s normal - inspect only ever
shows data
> > from the actual columns on the table. You can still call total (in
> > this case) to get that value.
>
> Hi Fred...
>
> By print, I basically am trying to get the output on the Rails console
> for a "non model attribute" in particular.. which I''m
unable to do ,
> yet..
like I said, just call the method. Just because it doesn''t show up in
the output from inspect doesn''t mean it''s not there.

Fred
>
> --
> Posted viahttp://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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Frederick Cheung wrote in post #1033717:
> On Nov 25, 3:05pm, "Mohnish J."
<li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote:
>> > from the actual columns on the table. You can still call total (in
>> > this case) to get that value.
>>
>> Hi Fred...
>>
>> By print, I basically am trying to get the output on the Rails console
>> for a "non model attribute" in particular.. which
I''m unable to do ,
>> yet..
>
> like I said, just call the method. Just because it doesn''t show up
in
> the output from inspect doesn''t mean it''s not there.
>
> Fred
Hi Fred,

Kindly correct me if I am wrong.. Are you saying I can use the inspect 
method to get the value of the aliased "total" attribute ?

I don''t think you meant that, but just confirming..

If not, how else would I be able to get the value of total as a count of 
all users residing in each area of a city?

I found an alternative with calling count towards the end of a Rails 
query, like this:-

User.select(area, count(area) as total).group(''area'').count

But I am not too sure how active record is taking count into 
consideration wrt the above case, and it would be more difficult for me 
to use it in complex queries without getting the basic understanding wrt 
''count'' in the above context..

Your inputs on this..?

Thanks..

-- 
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On Nov 26, 4:03 pm, "Mohnish J."
<li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>
wrote:
> Frederick Cheung wrote in post #1033717:
> Hi Fred,
>
> Kindly correct me if I am wrong.. Are you saying I can use the inspect
> method to get the value of the aliased "total" attribute ?
>
No
> I don''t think you meant that, but just confirming..
>
> If not, how else would I be able to get the value of total as a count of
> all users residing in each area of a city?
>
users = User.select(''area, count(area) as
total'').group(''area'').order(''total
DESC'').limit(30)
users[0].total

Fred
> I found an alternative with calling count towards the end of a Rails
> query, like this:-
>
> User.select(area, count(area) as
total).group(''area'').count
>
> But I am not too sure how active record is taking count into
> consideration wrt the above case, and it would be more difficult for me
> to use it in complex queries without getting the basic understanding wrt
> ''count'' in the above context..
>
> Your inputs on this..?
>
> Thanks..
>
> --
> Posted viahttp://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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Frederick Cheung wrote in post #1033844:
> On Nov 26, 4:03pm, "Mohnish J."
<li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote:
>> Frederick Cheung wrote in post #1033717:
>
>> Hi Fred,
>>
>> Kindly correct me if I am wrong.. Are you saying I can use the inspect
>> method to get the value of the aliased "total" attribute ?
>>
> No
>> I don''t think you meant that, but just confirming..
>>
>> If not, how else would I be able to get the value of total as a count
of
>> all users residing in each area of a city?
>>
> users = User.select(''area, count(area) as
> total'').group(''area'').order(''total
> DESC'').limit(30)
> users[0].total
>
> Fred
Hi Fred..

Thanks,

This worked like magic..:). I would like to deep dive into how exactly 
active record is dealing with storing such queries...processing them... 
Rails Guides was my one stop ref but , I couldn''t find trace for more 
examples dealing with such requirements...Where can I find more info on 
this ...?

I tried a users.inspect, it gave me no trace of a ''total''
attribute..
which data structure is being generated and used to store the result of 
this Rails query..?? Based on your answer..

Thanks again for your help...

-- 
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On Nov 27, 9:47 am, "Mohnish J."
<li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>
wrote:
> Frederick Cheung wrote in post #1033844:
> This worked like magic..:). I would like to deep dive into how exactly
> active record is dealing with storing such queries...processing them...
> Rails Guides was my one stop ref but , I couldn''t find trace for
more
> examples dealing with such requirements...Where can I find more info on
> this ...?
>
> I tried a users.inspect, it gave me no trace of a ''total''
attribute..
> which data structure is being generated and used to store the result of
> this Rails query..?? Based on your answer..
>
inspect is hardcoded to only display columns from the actual table.
All the attributes are stored int the attributes hash of the active
record object.

Fred
> Thanks again for your help...
>
> --
> Posted viahttp://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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Thanks a lot for your help 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.