0

Given the following models:

User
    id
UserPulses
    id, user_id, group_id, created_at

What I would like to do is obtain all of a user's UserPulses grouped by (group_id) and only obtain the most recent UserPulse per group_id. I've been able to do this by looping through group by group, but that takes a large number of queries. Is this possible with one query?

Something like: user.user_pulses.group("group_id)")

Any ideas? Thanks

AnApprentice
  • 108,152
  • 195
  • 629
  • 1,012

3 Answers3

2

You can't do this reliably through the usual ActiveRecord interface but you can do it through SQL using a window function. You want some SQL like this:

select id, user_id, group_id, created_at
from (
    select id, user_id, group_id, created_at, 
           row_number() over (partition by group_id order by created_at desc, id desc) as r
    from user_pulses
    where user_id = :user_id
) dt
where r = 1

and then wrap that in find_by_sql:

pulses = UserPulses.find_by_sql([%q{
    select id, user_id, group_id, created_at
    from (
        select id, user_id, group_id, created_at, 
               row_number() over (partition by group_id order by created_at desc, id desc) as r
        from user_pulses
        where user_id = :user_id
    ) dt
    where r = 1
}, :user_id => user.id])

The window function part essentially does a local GROUP BY with each group_id, sorts them (with id desc as the secondary sort key as a "just in case" tie breaker), and tacks the per-group row number in r. Then the outer query filters out the first in each group (where r = 1)and peels off the originaluser_pulses` columns.

mu is too short
  • 426,620
  • 70
  • 833
  • 800
  • Thank you, try to debug through it. Getting an error "PG::Error: ERROR: syntax error at or near ":" LINE 7: where user_id = :user_id " – AnApprentice Aug 22 '12 at 02:39
  • This appears to return an array which means I can't do pulses.where(:group_id => 1) to get the record from the array w/o requering the db. Is there a way to extract an item from this array? – AnApprentice Aug 22 '12 at 02:48
  • @AnApprentice: `find_by_sql` wants you to wrap the args in an Array when you use placeholders. I just added the missing `[` and `]`. Sorry about that. – mu is too short Aug 22 '12 at 02:50
  • 1
    @AnApprentice: You'd have to add that sort of thing to the SQL manually, ActiveRecord doesn't really understand anything more complicated than `select * from t where a = b`. – mu is too short Aug 22 '12 at 02:52
2

You can use the PostgreSQL specific extension of the SQL feature DISTINCT: DISTINCT ON

SELECT DISTINCT ON (group_id)
       id, user_id, group_id, created_at
FROM   user_pulses
WHERE  user_id = :user_id
ORDER  BY group_id, created_at DESC, id; -- id just to break ties

Simpler than window functions (but not as portable) and probably fastest.
More details under this related question.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

Something like this, perhaps. But there could be multiple records for a user/group_id combo if they share the same date.

SELECT p.id, p.user_id, p.group_id, p.created_at
  FROM UserPulses p
      ,( SELECT user_id, group_id, MAX(created_at) as max_date
           FROM UserPulses
           GROUP BY user_id, group_id ) m
  WHERE u.user_id = m.user_id
    AND u.group_id = m.group_id
    AND u.created_at = m.max_date
Glenn
  • 8,932
  • 2
  • 41
  • 54