What is the way to paginate last updated rows from multiple tables?

Question

I want to have something like a newsfeed from multiple objects sort by most_recent. However I need to paginate (load more/offset) the results.

• Row1 = Post's - text, username
• Row2 = Photo's - text, picture_url, username
• Row3 = Post's - text, username
• Row4 = Photo's - text, picture_url, username

I was thinking having a table inheritance called Activity that will hold all types of objects, referencing them with the columns item_type and item_id.

This way I query directly from the Activity table.

This should work. But how to maintain the activity to "stay tuned" of it's items "updated_at"?

When I comment in a post, should the comment update the post "updated_at" column and the post update the "updated_at" column on the activity?

Thanks!

EDIT Activities table:

  id   event      item_type   item_id   parent_type    parent_id    updated_at
  1   "create"      Post         1         Group           100
  2   "create"      Post         2         Group           100
  3   "create"     Comment       1         Post            1
  4   "create"      Photo        1         Group           100

Query: The latest root events (parent_type is "Group") from activities. Should bring Post 1, Photo 1 and Post 2.

SELECT * FROM activities WHERE
parent_type = "**Group**" and parent_id = 100
ORDER BY created_at

Still not retrieving the latest. Because the comments didn't affected the Post activity.

Answer 1

You may add a column timestamp and a trigger that update the column when the row is updated.

Then, sorting by that column will get your result.

Answer 2

I would recommend against updating the "updated_at" value for a post when someone comments on that post. Instead, keep the time that each record is inserted or edited on the record itself (main post, comment or reply on a post). Updating the parent record when a new child is inserted hides information and makes some types of questions difficult to answer.

You are better off keeping a separate activity table that records transactions relating to posts and comments being added or updated. This way you can join the content to the activity transaction and sort by the most recent (i.e. max) transaction date (group by item ID).

EDIT:

@Luccas - The issue is the design of your activity table. First, you seem to have left off any reference to time in your sample data, but your query references activities.created_at so I assume that column should be there. Also, I'm not sure I understand what you mean by parent_id - I may not understand what you intend there.

From what I can tell, what your table is missing is a reference to the parent post which must be populated every time. Activities which are posts should refer to themselves. Activities which are comments should refer to the posts that the comment applies to.

Then to get most recent posts you select from your posts list, filtering for type ="post" and joined to your activities list (for all types). You need to group by the columns you're using from the post and use the join to select max(created_at) from the unfiltered activities. You then sort the whole thing by this date/time descending.

This way you select only posts and sort them by the latest time for each post.