Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers 2

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.

share|improve this answer
    
but is this a good approach? If so then it works. Its similar the way I was thinking –  Luccas Jan 18 '13 at 19:17
add comment

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.

share|improve this answer
    
Thanks for the answer. But take a look at my update on the question and how I think your idea will not fit, I guess. –  Luccas Jan 20 '13 at 18:40
    
@Luccas - See my edit. It should explain how to tweak your design and how to access the information you are looking for. –  Joel Brown Jan 20 '13 at 19:17
    
Sorry my edit, the parent type should be a Group. The parent_type and parent_id are the first level up-tree of an item. For a Comment, the parent is a Post. For a Post is a Group. For a Photo the parent is a Group. In case of a Photo, its parent is itself? Then the query should do select * from activities where parent_type = 'Post' or 'Photo'.... But its seems not too good –  Luccas Jan 20 '13 at 20:53
    
@Luccas - To make it easier to achieve what you want to do, I would keep a column that always points to a post. If the record is a post, then it should point to itself. If the record is something that applies to a post (like a comment) then point to the post on which the comment was made. This lets you attach all activities (at any level) to the posts, which is what you want. If you know exactly how many levels deep your hierarchy can go (i.e. no comment on a comment, etc) then you can also use UNION to combine multiple selects for each depth of hierarchy. Copying the post_id is easier. –  Joel Brown Jan 20 '13 at 21:57
    
And if the record is a Photo? –  Luccas Jan 20 '13 at 22:58
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.