# Converting SQL query to Rails query for a non model attribute

**URL:** <https://www.ruby-forum.com/t/converting-sql-query-to-rails-query-for-a-non-model-attribute/214206>\
**Category:** Rails\
**Created:** [November 24, 2011, 1:28pm UTC](https://www.ruby-forum.com/t/converting-sql-query-to-rails-query-for-a-non-model-attribute/214206 "2011-11-24T13:28:43Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![luislavena](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/0ea827/32.png) [@luislavena](https://www.ruby-forum.com/u/luislavena)\
**Post date:** [November 24, 2011, 1:28pm UTC](https://www.ruby-forum.com/t/converting-sql-query-to-rails-query-for-a-non-model-attribute/214206/1 "2011-11-24T13:28:43Z")

</div>

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](http://railscasts.com/episodes/202-active-record-queries-in-rails-3) 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…

---

<div class="post-metadata">

**Author:** ![mohnish](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/919ad9/32.png) [@mohnish](https://www.ruby-forum.com/u/mohnish)\
**Post date:** [November 24, 2011, 2:17pm UTC](https://www.ruby-forum.com/t/converting-sql-query-to-rails-query-for-a-non-model-attribute/214206/2 "2011-11-24T14:17:32Z")

</div>

On Nov 24, 12:28pm, “Mohnish J.” [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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.

---

<div class="post-metadata">

**Author:** ![mohnish](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/919ad9/32.png) [@mohnish](https://www.ruby-forum.com/u/mohnish)\
**Post date:** [November 25, 2011, 4:05pm UTC](https://www.ruby-forum.com/t/converting-sql-query-to-rails-query-for-a-non-model-attribute/214206/3 "2011-11-25T16:05:44Z")

</div>

Frederick C. wrote in post #1033538:

> On Nov 24, 12:28pm, “Mohnish J.” [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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..

---

<div class="post-metadata">

**Author:** ![mohnish](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/919ad9/32.png) [@mohnish](https://www.ruby-forum.com/u/mohnish)\
**Post date:** [November 25, 2011, 6:20pm UTC](https://www.ruby-forum.com/t/converting-sql-query-to-rails-query-for-a-non-model-attribute/214206/4 "2011-11-25T18:20:57Z")

</div>

On Nov 25, 3:05pm, “Mohnish J.” [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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

---

<div class="post-metadata">

**Author:** ![mohnish](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/919ad9/32.png) [@mohnish](https://www.ruby-forum.com/u/mohnish)\
**Post date:** [November 26, 2011, 5:03pm UTC](https://www.ruby-forum.com/t/converting-sql-query-to-rails-query-for-a-non-model-attribute/214206/5 "2011-11-26T17:03:39Z")

</div>

Frederick C. wrote in post #1033717:

> On Nov 25, 3:05pm, “Mohnish J.” [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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..

---

<div class="post-metadata">

**Author:** ![mohnish](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/919ad9/32.png) [@mohnish](https://www.ruby-forum.com/u/mohnish)\
**Post date:** [November 26, 2011, 7:32pm UTC](https://www.ruby-forum.com/t/converting-sql-query-to-rails-query-for-a-non-model-attribute/214206/6 "2011-11-26T19:32:36Z")

</div>

On Nov 26, 4:03pm, “Mohnish J.” [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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

---

<div class="post-metadata">

**Author:** ![mohnish](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/919ad9/32.png) [@mohnish](https://www.ruby-forum.com/u/mohnish)\
**Post date:** [November 27, 2011, 10:47am UTC](https://www.ruby-forum.com/t/converting-sql-query-to-rails-query-for-a-non-model-attribute/214206/7 "2011-11-27T10:47:24Z")

</div>

Frederick C. wrote in post #1033844:

> On Nov 26, 4:03pm, “Mohnish J.” [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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…

---

<div class="post-metadata">

**Author:** ![mohnish](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/919ad9/32.png) [@mohnish](https://www.ruby-forum.com/u/mohnish)\
**Post date:** [November 27, 2011, 12:30pm UTC](https://www.ruby-forum.com/t/converting-sql-query-to-rails-query-for-a-non-model-attribute/214206/8 "2011-11-27T12:30:26Z")

</div>

On Nov 27, 9:47am, “Mohnish J.” [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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

---

<div class="post-metadata">

**Author:** ![mohnish](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/919ad9/32.png) [@mohnish](https://www.ruby-forum.com/u/mohnish)\
**Post date:** [November 28, 2011, 3:43pm UTC](https://www.ruby-forum.com/t/converting-sql-query-to-rails-query-for-a-non-model-attribute/214206/9 "2011-11-28T15:43:02Z")

</div>

Thanks a lot for your help Fred…