Converting SQL query to Rails query for a non model attribute

rubyonrails-talk
1

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

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

2

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]:#202 Active Record Queries in Rails 3 - RailsCasts

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

3

Frederick Cheung wrote in post #1033538:

4

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

5

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

6

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

7

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

8

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

9

Thanks a lot for your help Fred...