Accessing a non model attribute through Rails Active Record Query Interface

Hi,

Imagine this is your scenario:-

You have a model called User. This users table in the back end has more

than 100 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)

Rails Active Record Query Interface?

This gives me the areas in descending order, but it doesn't share the

count in terms of number of residents per area. Atleast not a direct outcome of the equivalent Rails command.

Now here come a few road blocks, something that I faced atleast..:)

1. How can we access the count attribute "total" through

2. How can we do 1. along with getting the TOP 30 records ?

3. How can we do 1., 2. and also get the records in the descending order ?

To get the count for each area the below query works, but it doesn't directly support our requirements as of 2 and/or 3.

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

The reason behind this is,

a. Profile.select('location, count(location) as total').group('location').order('total DESC').count

b. Profile.select('location, count(location) as total').group('location').order('total DESC').limit(30).count

a. and b. throw errors via Rails console.

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 was unable to print it

using the above Rails query. 


I'm able to get the count of residents belonging to each area using `User.select('area, count(area) as total').group('area').count`, 
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 couldn'tfind much from the Rails Guides on this .

Now what worked for me like a charm thanks to inputs from Frederick Cheung(via the following thread - http://www.ruby-forum.com/topic/3073356) was:-

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

users[0].total

This is how through a loop we can access the total number of folks residing in other areas as well.

Hope this helps.

Thanks..:)