mef_ mef_ - 25 days ago 7
MySQL Question

Can't select rows grouping by created_at

I have the table:

+----+----------------------+---------------------+
| id | implemented_features | created_at |
+----+----------------------+---------------------+
| 1 | 19 | 2013-07-18 04:10:12 |
| 2 | 6 | 2013-07-18 04:10:12 |
| 3 | 26 | 2013-07-19 04:10:12 |
| 4 | 11 | 2013-07-19 04:10:12 |
| 5 | 1 | 2013-07-20 04:10:12 |
+----+----------------------+---------------------+


When I query this directly via MySQL it works perfectly as I want

select date(created_at) as date, sum(implemented_features) as sum from summaries group by date(created_at);


But when I try to convert this query to ActiveRecord syntax it returns me nil.

2.0.0p0 :035 > Summary.select("date(created_at) as date, sum(implemented_features)").group("date(created_at)")
Summary Load (0.5ms) SELECT date(created_at) as date, sum(implemented_features) FROM `summaries` GROUP BY date(created_at)
=> #<ActiveRecord::Relation [#<Summary id: nil>]>


As you see, final queries are equal in both examples.
Why it don't work in case of ActiveRecord?

Using Rails 4.0.0, Ruby 2.0, mysql db in my rails project.

Answer

I think you're just a little confused by the console output.

You're saying this:

Summary.select("date(created_at) as date, sum(implemented_features)")...

so the returned Summary instances (wrapped up in an ActiveRecord::Relation) don't have any of the usual Summary attributes: no id, no created_at, no implemented_featured, etc. When you call inspect on an ActiveRecord object, it wants to show you what's inside the object and that means that it wants to show you the contained database attributes; your Summary instances don't have any of the usual attributes so you see things like <Summary id: nil>.

Fear not, the values you selected really are there. If you say:

Summary.select(...).map(&:date)

you should see the date(created_at) as date values. If you add an alias for the sum(implemented_features) then you can extract the sums by using that alias as a method name.

Comments