Currently, I am developing a social media platform similar to Twitter but have encountered an issue with sorting.
Below are the collections I am working with:
Tweets:
createdAt: Date
body: String
userId: String
Events:
createdAt: Date
type: String (either 'retweet' or 'like')
tweetId: String
userId: String
Whenever a user retweets a Tweet, a new Event document is generated to keep track of the tweet and user IDs.
When visiting a user's profile page, I search for all Tweets written by the user or that have been retweeted by the user. To ensure all necessary tweets are available, a reactive join is performed on the server so the Tweets collection contains all relevant information.
The challenge lies in sorting: currently, all Tweets are sorted based on their createdAt value. This results in retweeted tweets from previous days appearing below the user's recent tweets.
In essence, I need a method to sort Tweets either by their own createdAt field or by the createdAt field of an associated Event if one exists.
I am open to restructuring my models if there is a more efficient approach. One idea was to create a new Tweet each time a retweet occurs, but this poses issues with maintaining accurate counts for likeCount and retweetCount. If every retweet generates a new Tweet and subsequent likes or retweets occur, it would lead to incorrect numbers or require excessive denormalization updates.