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?
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.