Public activity select unique foreign keys by created time

Question

I'm looking to do a query for an activity object in my app. I'm using the public_activity gem and I have a list of activities which are connected to several models and I would like to list out the most recent unique activity.

Basically I would like to query the most recent activity for a particular tracking. Let's say that I'm tracking the like button and it's ID is 29. I want to query out the most recent (one) instance of tracking ID=29 and other tracking IDs. I don't know the querying for rails too well. Right now this is the only line I have which displays all activity:

@activities = PublicActivity::Activity.order("created_at desc").where(owner_id: current_user.id)

Let's say I have a table of activities as follows:

id  activity_id description created_on
1   3           Like        yesterday
2   3           Unlike      two days ago
3   6           Comment     yesterday
4   7           Review      yesterday
5   7           Review      two days ago

I want to pull out the most recent entries of each activity ID into the @activities variable like the following:

id  activity_id description created_on
1   3           Like        yesterday
3   6           Comment     yesterday
4   7           Review      yesterday

Does public_activity have a workaround for this?

I've added what I want to pull out, those are just dummy data but my point is made — Syafiq Kamarul Azman, Apr 25 '14 at 06:14

score 0 · Answer 1 · answered Jan 05 '16 at 03:06

You can use a combination of group and select to accomplish this.

PublicActivity::Activity.
  select("MAX(created_at) as when, trackable_type").
  group("trackable_type").
  where(owner_id: current_user.id)

Here's the output

+-------------------------+-------------------------+
| trackable_type          | when                    |
+-------------------------+-------------------------+
| Report                  | 2015-11-27 20:25:31 UTC |
| QuickAnswer             | 2016-01-04 23:36:21 UTC |
| Comment                 | 2015-10-23 15:35:32 UTC |
| TakenExam               | 2016-01-04 23:46:11 UTC |
| SeenVideo               | 2015-08-23 21:27:58 UTC |
| Provider                | 2015-09-17 23:47:40 UTC |
| AnswerForRecommendation | 2015-08-18 15:13:03 UTC |
| Subscription            | 2015-08-24 08:37:08 UTC |
+-------------------------+-------------------------+

Public activity select unique foreign keys by created time

1 Answers1