Wednesday, February 15, 2012

Accessing a non model attribute through Rails Active Record Query Interface


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:-'area, count(area) as total').group('area').order('total

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.'area, count(location) as total').group('area').count
The reason behind this is,
a.'location, count(location) as total').group('location').order('total DESC').count
b.'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. 


When I used the to_sql command in my Rails query, I got:-'area, count(area) as total').group('area').order('total


    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 `'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 - was:-

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


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

Hope this helps.


No comments:

Post a Comment