Model-less SQL results

Rails
1

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

2

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

3

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