How to group records by day in Rails using Postgres

Question

So I've found these questions:

And this gem: https://github.com/ankane/groupdate

I'm looking to group records by day, in a format like this:

[
  { "2016-03-16" => [Record1, Record2, Record3] },
  { "2016-03-17" => [Record1, Record2] },
  { "2016-03-18" => [Obj1, Obj2] }
]

I've been able to get this format using this code:

def group_by_criteria
  created_at.to_date.to_s
end

list.group_by(&:group_by_criteria).map {|k,v| { k => v} }

However, as explained in other questions, this is not very efficient for a large number of records, I think I should be using grouping from the db, but I'm not sure how to get the format I'm looking for, I tried something like this but I don't get what I'm expecting:

list.order("date_trunc('day', created_at) DESC).map{ |k, v| { k => v }}

How can I group records by day from the db like this?

The only other way I can see doing this would involve a query per day, which isn't terrible, but is more simple/efficient than doing it in ruby. However it can tax your DB. But then you can add caching, and that can aid. So many variables, it really depends on your use cases. — omarvelous, Mar 08 '16 at 17:08

score 5 · Accepted Answer · answered Mar 08 '16 at 16:51

Because you ultimately do want to load all records into memory, your first solution (grouping in Ruby) is actually the best you can do. Leveraging SQL grouping could help optimize if you wanted to just, say, get a count of records in each group, or even a comma-separated list of record names, but -- since you actually want the records -- there's no way to get around the fact you'll simply have to fetch all the records.

How to group records by day in Rails using Postgres

1 Answers1