Newsfeeds

I started implementing a newsfeed-type functionality. Essentially, any action performed on the site by the user would show on the user’s own activity and in the feeds of those who follow the user.

I looked for the best way to implement this on StackOverflow, and came up with the following flow…

1. Actions on the site make an entry into a logs table.
2. The log saves the action type (follow, job posting, answer…) along with the source type and id (user id and users table reference for example) and target type and id.
3. Load the user’s logs.
4. Arrange the logs by table they reference for the data and make the queries to get the data about the referenced action.
5. Pass the data to a function that generates the language and the markup for the log in the newsfeed the users see.

SELECT source_table, source_id, target_table, target_id, action, action_parameter, date_created FROM logs WHERE source_table = ? AND source_id = ? ORDER BY date_created desc

When loading activity from logs, we select the logs and group them by target_table and then query for the extra data foe each group based on the target tables.

When retrieving data of the same type however such as all jobs… we can just join on the data tables when fetching the logs

SELECT j.id as job_id, j.*, GROUP_CONCAT(tx.tag_name) as tags FROM jobs_access a LEFT JOIN jobs j ON j.id = a.job_id LEFT JOIN tags t ON t.parent_id = j.id AND parent_table ='jobs' LEFT JOIN taxonomy tx ON tx.id = t.tag_id WHERE a.user_id = ? GROUP BY j.id ORDER BY j.status, j.date_created desc

To improve the activity example, we could join on all that tables that the logs can be made for in theory, but it would have unnecessary joins for many cases and it would not be dynamic… when adding new types of logs, the query would need to be modified.

Any thoughts or ideas on improving this? Let me know on HN:
http://news.ycombinator.com/item?id=5160542