I'm building an activity log of users actions. But the main objective is to get all activities not per user, but per page (scope) its inside. I will explain more after.
The main thing I'm concern about and making this question is to avoid joins to perform this and walk to the right way with scalability
The well known designs approaches for activity logs:
- What's the best manner of implementing a social activity stream?
- How to implement the activity stream in a social network
So, to get all user activities its all about fetching the table with the column "user_id = ?". Ok.
Well. Now suppose my app has many pages about celebrities. User can add topics, questions (inside topics) into a page. Also change page's name, photo etc.
An hypothetical example of a page: /stevejobs
Well, the activities are only relevant when grouped by the /stevejobs page, because they will need appear inside the page:
Page: Stevejobs
Actions:
- User 1 add a question "When were Steve Jobs born?" on topic "About"
- User 2 edited page name to "Steve Jobs"
- User 1 added a topic "Life"
So far each activity has the object related to the activity and the parent object. E.g:
When add question: {object_type: Question, object_id: 1, parent_type: Topic, parent_id: 1}
When add topic: {object_type: Topic, object_id: 1, parent_type: Page, parent_id: 1}
Then the column parent can have many levels of depth until the page id to join if I want to get all activities that happened in /stevejobs
One solution is to add 2 extra polymorphic columns named "scope_type" and "scope_id".
When add question: {object_type: Question, object_id: 1, parent_type: Topic, parent_id: 1, scope_type: Page, scope_id: 1}
When add topic: {object_type: Topic, object_id: 1, parent_type: Page, parent_id: 1, scope_type: Page, scope_id: 1}
So its possible to get all activities per page more easily, I think
What can I deal with it?
The joins that I'm doing..
# All activities from Topics of page list_activities = Activity.joins("JOIN lists ON activities.item_id = topics.id and activities.item_type = 'Topic'") .joins("JOIN pages ON pages.id = topics.page_id") .where("pages.id = #{self.id}") # All activities from Question of Topic of Page document_activities = Activity.joins("JOIN documents ON activities.item_id = questions.id and activities.item_type = 'Question'") .joins("JOIN topics ON topics.id = questions.topic_id") .joins("JOIN pages ON pages.id = topics.page_id") .where("pages.id = #{self.id}")
And then make a union. Too ugly. And in the end the serialized columns too avoid joins are useless
