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
by Ryan B…

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…

On Nov 24, 12:28pm, “Mohnish J.” [email protected] 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.

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.

Frederick C. wrote in post #1033538:

On Nov 24, 12:28pm, “Mohnish J.” [email protected] 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…

On Nov 25, 3:05pm, “Mohnish J.” [email protected] 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

Frederick C. wrote in post #1033717:

On Nov 25, 3:05pm, “Mohnish J.” [email protected] 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…

On Nov 26, 4:03pm, “Mohnish J.” [email protected] wrote:

Frederick C. 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 C. wrote in post #1033844:

On Nov 26, 4:03pm, “Mohnish J.” [email protected] wrote:

Frederick C. 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…

On Nov 27, 9:47am, “Mohnish J.” [email protected] wrote:

Frederick C. 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…

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs