A twitter like Feed

Hi, I have some problem resolving an SQL request problem. And I wonder if that is even possible. Let's imagine a twitter user page. On that page, they are the tweets a user has posted, but also the tweets a user has retweeted. But not in the order of the initial tweet, but in the order of the tweet/retweet.

Let's take an example. Let's say, Bootstrap sends a tweet. After that, I tweet something. And after that, I retweet the Bootstrap tweet. My user profile will show the bootstrap tweet in first position, then my tweet. So the ordering of the tweets take in consideration the time of the retweet, not the time from the initial tweet. Otherwise, I would have my tweet in first position, and the bootstrap one in second position.

So I have three tables:

users:
    - id
    - username
tweets: 
   - id
   - user_id
   - content
   - timestamps
retweet_user:
   - tweet_id
   - user_id
   - timestamps

What happens, is that when a user tweets something, it doesn't have an entry in the "retweet_user" table, because it already has an entry on the tweet table (with user_id).

I managed to make this request, which lists the tweets that I want, but not in the regular order obviously:

$tweets = Tweet::with('user')
    ->where('user_id',$user->id)
    ->orWhereHas('retweeters', function($query) use($user) {
        $query->where('id',$user->id);
    })->orderBy('id','desc')->get();

So here are the two solutions I found for my problem:

• The easy one: When a user tweets, an entry is also made in the retweet_table, and I juste have to sort the results by the timestamp of this table.
• The one I would like to do: Order the results by a virtual field called "time" for example. If the result is from the first where('user_id',$user->id'), it stores the created_at from the tweets table. If the result is from the orWhereHas(...), it stores the value of the created_at from the pivot table. I just would have to orderBy this virtual field, and it would work! Is this possible?

Thanks for the advices, and let me know if I was not clear in my explanations,

Many thanks! :)