Hi everyone,
I'm currently logging all actions of users and want to display their actions for the people following them to see - kind of like Facebook does it for friends.
I'm logging all these actions in a table with the following structure:
- id - PK
- userid - id of the user whose action gets logged
- actiondate - when the action happened
- actiontypeid - id of the type of action (actiontypes stored in a different table - i.e. following other users, writing on people's profiles, creating new content, commenting on existing content, etc.)
- objectid - id of the object they just created (i.e. comment id)
- onobjectid - id of the object they did the action to (i.e. id of the content that they commented on)
Now the problem is there are several types of actions that get logged (actiontypeid).
What would be the best way of retrieving the data to display to the user?
The easiest way out would be gabbing the people the user follows dataset and then just go from there and grab all other info from the other tables (i.e. the names of the users the people you're following just started following, names of the user profiles they wrote on, etc.). This however would create a a huge amount of small queries and trips to the database in a while loop. Not a good idea.
I could use joins to retrieve everything in one massive dataset, but how would I know where to grab the data from in just one query? - there's different types of actions that require me to look into several different tables to retrieve data, based on the actiontypeid...
i.e. To get User X is now following User Y I'd have to get my data (User Y's username) from the followers table, whereas User X commented on content Y would need me to look in the content table to get the content's title and url.
Any tips are welcome, thanks!