0

I'm using Postgres with Ruby on Rails and would like to query my posts table in away that returns all posts grouped by the day they were created_at, with each group in chronological order.

A perfect example of my goal is any blog that's index lists all posts by date.

The pseudo-code object structure i expect to get back would be something similar to this:

posts_grouped_by_date = {
  "1/2/2014" => [post1, post2, post3],
  "1/15/2014" => [post5, post6,post7],
  etc..
}

My table called posts and looks like this:

 id |                               title                               |         created_at
----+-------------------------------------------------------------------+----------------------------
 13 | Eaque hic tenetur at quam quibusdam eveniet veritatis.            | 2014-08-18 02:40:31.329665
  9 | The only thing i want to say.                                     | 2014-08-18 02:40:31.329665
 16 | Est sunt cumque rerum nam nobis consequuntur aut.                 | 2014-08-19 02:58:26.817936
 18 | Fuga assumenda facilis aut mollitia eum soluta.                   | 2014-08-19 02:58:26.837839
  8 | The only thing i want to say.                                     | 2014-08-18 02:40:31.329665
 12 | Laudantium dolores odit sed veritatis in et vel deserunt.         | 2014-08-18 02:40:31.329665
 10 | The only thing i want to say.                                     | 2014-08-18 02:40:31.329665
 20 | Rem laboriosam ipsam sunt maxime quia adipisci et voluptatem.     | 2014-08-19 02:58:26.837839
 19 | Laudantium sit explicabo et quia eligendi ipsam.                  | 2014-08-19 02:58:26.837839
 21 | Repellat qui eos dolorem.                                         | 2014-08-19 02:58:26.837839
  6 | The only thing i want to say.                                     | 2014-08-18 02:40:31.329665
 17 | Error voluptatem architecto distinctio impedit iste dolores enim. | 2014-08-19 02:58:26.829968
 15 | Vero modi voluptate deleniti labore quis est.                     | 2014-08-19 02:58:26.808126
 14 | Et neque porro qui animi facilis numquam.                         | 2014-08-19 02:58:26.800474

How would i do this using ActiveRecord or Arel syntax? An explanation behind the code would be very helpful too. i'm using this for learning/practice rather than just looking to copy/paste.

SharkLaser
  • 713
  • 1
  • 10
  • 19

1 Answers1

0

I think you want something like this:

Post.order(:created_at).group_by {|p| p.created_at.to_date}

1 - Post.order(:created_at) orders the posts by the created_at attribute in ascending order.

2 - Then, once you have all the posts ordered you group them using the code in the block. Since you don't want the timestamp included in the grouping (each second would have a different record, so what's the point?), you convert the timestamp to just date.

Tiago Farias
  • 3,397
  • 1
  • 27
  • 30
  • This works but it requires brings everything into Ruby before running `group_by`. Isn't there a way to make the SQL server to do the work? – SharkLaser Aug 19 '14 at 06:05
  • Hmm. You would have to use a native function from your db. I don't know about SQL Server, but here's a guy who solved it with MYSql (just for reference) http://stackoverflow.com/questions/25195591/mysql-rails-activerecord-date-grouping-and-timezones – Tiago Farias Aug 19 '14 at 06:21