How to retrieve the most recent Active Record objects per column combination?

Question

I have a model called Event which belongs to Area and Task. I'm attempting to retrieve a collection of events that only contains the most recent event per area and task combination. That is, I only want the most recent event of the events that have the same area_id and task_id. Example collection of events:

|event_id|area_id|task_id| ... |
|--------|-------|-------|-----|
|      5 |     3 |     2 | ... |
|      4 |     3 |     1 | ... |
|      3 |     3 |     2 | ... |

Here I want only event 5 and 4 to be returned since 3 is older.

I've tried using Event.select(:area_id,:task_id).distinct which seems to work, but strips all other attributes of the returned events, including :id. Grateful for any help or suggestions!

What database are you using? – Ilya Lavrov Apr 08 '17 at 16:02 — Ilya Lavrov, Apr 08 '17 at 16:02

Gerry · Accepted Answer · 2017-04-10T14:19:54.080

2

You can use raw SQL inside select, so you could try something like this:

Event.select("DISTINCT(CONCAT(area_id, task_id)), id, attr1, attr2")

Where id, attr1 and attr2 are the other attributes from your Event table.

Or you could use .group instead of .distinct and forget about using raw SQL:

Event.all.group(:area_id,:task_id)

You will get the same result as using DISTINCT and all attributes will be available.

UPDATE

To order before grouping, you can use find_by_sql with nested queries (again, raw SQL):

Event.find_by_sql(
  "SELECT * FROM (
    SELECT * FROM `events`
    ORDER BY `events`.`created_at`) AS t1
  GROUP BY t1.`area_id`, t1.`task_id`";
)

edited Apr 10 '17 at 14:19

answered Apr 08 '17 at 15:55

Gerry

10,337
3
31
40

Thanks, that doesn't seem to work for the MySQL I'm using: ActiveRecord::StatementInvalid: Mysql2::Error: Operand should contain 1 column(s): SELECT DISTINCT(area_id, task_id), id FROM `events` – Ciryon Apr 09 '17 at 08:41
You could use `CONCAT` to turn both columns into one and will work fine. I updated my answer to consider that and also added another solution using `.group` (which i like better). – Gerry Apr 09 '17 at 11:51
I tried the `.group`alternative, but it picked up the oldest Event and I can't seem to order before group. – Ciryon Apr 10 '17 at 05:30
You're right, you can't order before grouping in SQL, you will need nested queries (`ORDER BY` and then `GROUP BY`) to accomplish that. I'll post new update with a nested query solution using `find_by_sql`. – Gerry Apr 10 '17 at 14:11

score 0 · Answer 2 · edited May 23 '17 at 12:09

In another words you need to group events by area_id and task_id, and select recent record in each group. There question about building sql-query for this case. PostgreSQL version for your table will be like this:

SELECT DISTINCT ON (area_id, task_id) events.*
FROM events
ORDER BY area_id ASC, task_id ASC, created_at DESC

And Rails code for this query:

Event.
  select("DISTINCT ON (area_id, task_id) events.*").
  order("area_id ASC, task_id ASC, created_at DESC")

How to retrieve the most recent Active Record objects per column combination?

2 Answers2