'm trying to design an activity feed, but I have a small problem.
Here's my current table.
id user_id status activitytype_id (status, video, photo, etc) created_at location video I didn't add some unrelated columns to make it short.
This works when I want to create a feed for the friends' posts of a user. I basically create an array that contains id's of friends of the user and check for user_id's in the activity table and sort them by date.
But a user also follows groups and events, this is the tricky part.
What's the best way to include them (groups and events) in the feed? I can add a column to the activity called event_post and group_post and add posts id's as foreign keys to the activity table (null by default), but I'd like to hear your opinions.
If I query the community_user table (user_id and community_id as foreign keys) and get the list of groups a user follows, I will need to create a second query for group activity from the activities table in addition to user activity. Can I merge them together (they both have created_at columns since they're in the same table) and order by date? I'm new to laravel and I want to learn whether it's possible or not, also a good way or not.
I have also another question, should I keep group posts and event posts in another table like community_post and reference it on activities table or it is okay to hold them in activities table?
id communitytype_id (group or event) name created_at user_id (FK from users table, owner of the group or event)