# Model-less SQL results

**URL:** <https://www.ruby-forum.com/t/model-less-sql-results/75662>\
**Category:** Rails\
**Created:** [November 12, 2006, 1:39am UTC](https://www.ruby-forum.com/t/model-less-sql-results/75662 "2006-11-12T01:39:00Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![craig\_b](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/76d3ee/32.png) [@craig\_b](https://www.ruby-forum.com/u/craig_b)\
**Post date:** [November 12, 2006, 1:39am UTC](https://www.ruby-forum.com/t/model-less-sql-results/75662/1 "2006-11-12T01:39:00Z")

</div>

I’m trying to get some stats from my database about my model, and I’m  
not sure how to go about it.

If I have a model ‘foo’ that has an attribute ‘color’ and I want to  
get some counts on this I could use the sql:

SELECT color, COUNT(\*) AS count  
FROM items  
GROUP BY status

which would return something like:  
±-------±------+  
| color | count |  
±-------±------+  
| blue | 3 |  
| green | 1 |  
| orange | 1 |  
| red | 4 |  
±-------±------+

How to translate this into:

def stats

# return a structured result I can iterate over in a view

somewhere…

# like and array of hashes: {[:color =\> ‘blue’, :count =\> 3],

[:color =\> ‘green’, :count =\> 1]…}  
end

The usual Model.find\_by\_sql wouldn’t work as I’m not returning a  
model, and I’m not seeing anything jump out at me like  
ActiveRecord::Base::Connection.execute\_sql for doing this sort of  
arbitrary sql execution.

Any suggestions? Am I missing something really obvious?

–  
Craig B.

AIM: kreiggers

---

<div class="post-metadata">

**Author:** ![craig\_b](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/76d3ee/32.png) [@craig\_b](https://www.ruby-forum.com/u/craig_b)\
**Post date:** [November 12, 2006, 12:21pm UTC](https://www.ruby-forum.com/t/model-less-sql-results/75662/2 "2006-11-12T12:21:36Z")

</div>

You can do stuff like Item.count :select =\> ‘color’, :group =\> ‘status’  
Which would return you an array of arrays, each containing [color,  
count].

For more complicated stuff i’ve usually resorted to  
ActiveRecord::Base::Connection#select\_all or select\_values

Fred

---

<div class="post-metadata">

**Author:** ![craig\_b](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/76d3ee/32.png) [@craig\_b](https://www.ruby-forum.com/u/craig_b)\
**Post date:** [November 12, 2006, 7:56pm UTC](https://www.ruby-forum.com/t/model-less-sql-results/75662/3 "2006-11-12T19:56:51Z")

</div>

On Nov 12, 2006, at 3:21 AM, Frederick C. wrote:

> You can do stuff like Item.count :select =\> ‘color’, :group =\>  
> ‘status’  
> Which would return you an array of arrays, each containing [color,  
> count].
> 
> For more complicated stuff i’ve usually resorted to  
> ActiveRecord::Base::Connection#select\_all or select\_values
> 
> Fred

Ahh! That was exactly the thing I was looking for!

## I ended up digging through the docs and playing with the console to do it the hard way using connection.execute(sql) and then putting the result into a hash. Somehow I’d forgotten that there was an easy way…

Craig B.

AIM: kreiggers