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

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

Frederick Cheung wrote in post #1033538:

Frederick Cheung wrote in post #1033538:

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

Frederick Cheung wrote in post #1033717:

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

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

Frederick Cheung wrote in post #1033844:

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

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 a lot for your help Fred...