add a column count(id) to the select statement, rail way

rubyonrails-talk
1

I would like to do something like this: SELECT *,count(ID) FROM downloads GROUP BY file_path ORDER BY user_id LIMIT 10;

I know it can be done with find_by_sql, but don't know if that can be done by just find.

Or is there better Rails way to do this?

Thanks.

2

I think you risk running 2 queries if you do it the rails way of Model.count(). Up to you really. Its probably better to do a custom find_by_sql to limit the queries you run against your DB.

Adam

3

I would like to do something like this: SELECT *,count(ID) FROM downloads GROUP BY file_path ORDER BY user_id LIMIT 10;

I know it can be done with find_by_sql, but don't know if that can be done by just find.

Or is there better Rails way to do this?

If by "Rails way" you mean something built in to ActiveRecord, then you're talking about counter_cache.

class Download < ActiveRecord::Base   belongs_to :product, :counter_cache => true end

Then in your product table, you would have a downloads_count column (int, default of 0).

When you do that you'll be able to access   product.downloads.size

*WARNING* I recall there being a prior thread talking about a bug in save() that blows away the count, or something like that. So I don't know if this solution is broken or not at this point.

- Mark.

4

Thanks for replies, I will have a look. But this find_by_sql solution is working like a charm for me... So, I might just leave it that way...