Limit in Group - ActiveRecord Postgres

Question

I have an events table I'm querying by month and trying to limit the number of events returned per day to 3.

[39] pry(#<EventsController>)> @events.group("DATE_TRUNC('day', start)").count

CACHE (0.0ms)  SELECT COUNT(*) AS count_all, DATE_TRUNC('day', start) AS
date_trunc_day_start FROM "events"  WHERE ((start >= '2014-08-31 00:00:00' and
start <= '2014-    10-12 00:00:00'))  GROUP BY DATE_TRUNC('day', start)
=> {2014-09-24 00:00:00 UTC=>5,
 2014-09-18 00:00:00 UTC=>6,
 2014-09-25 00:00:00 UTC=>3}

Here we have 5 events on the 24th, 6 on the 18th, and 3 on the 25th. http://stackoverflow.com/a/12529783/3317093>

When I try the query without the .count, I get the error message

PG::GroupingError: ERROR:  column "events.id" must appear in the GROUP BY clause or be used in an aggregate function

I looked at using select() to get the grouping to work, but would need to list all the columns in the table. How should I structure the query/scope to return 3 records from each group of events?

Edit - I'm close! I've found many similar questions, most of them in MySQL using select. I think using select could be the way to go, either as events.* or as below

@events.where("exists (select 1 from events GROUP BY DATE_TRUNC('day', start) limit 3)")

yields the SQL

SELECT "events".* FROM "events"  WHERE ((start >= '2014-08-31 00:00:00'
and start <= '2014-10-12 00:00:00')) AND (exists (select 1 from events
GROUP BY DATE_TRUNC('day', start) limit 3))

The query returns all @events sorted by id (seems :id is implicitly a part of the grouping). I've tried switching things up but most often get the same grouping error as earlier.

Answer 1

For anyone experiencing a similar issue, I would recommend checking out window functions and this blog post covering different ways to solve a similar question. The three approaches covered in the post include using 1) group_by, 2) SQL subselects, 3) window functions.

My solution, using window functions:

@events.where("(events.id)
  IN (
    SELECT id FROM
      ( SELECT DISTINCT id,
                        row_number() OVER (PARTITION BY DATE_TRUNC('day', start) ORDER BY id) AS rank 
      FROM events) AS result
    WHERE (
      start >= '#{startt}' and
      start <= '#{endt}' and
      rank <= 3
    )
  )
")

Answer 2

If you don't want to use count, you can use group_by from rails for list events as the following:

hash = @events.group_by{ |p| p.start.to_date}

And use this code for limit(3) for each date:

hash.inject({}){ |hash, (k, v)| hash.merge( k => v.take(3) )  }

Helper link for map on hash, and return hash instead of array.