Patrick Patrick - 22 days ago 7
MySQL Question

Algorithm that gets latest inputs in database based on two different factors

I have a website build upon laravel. The website consist of users, and users can follow each other.

Users
can make
Shoutouts
(like a tweet), and the users that are following each other should be able to see these. I have a
User
table in my database, and a
Shoutout
table, and a
Follow
table.

As it is made i Laravel, the User has a
hasMany
relationship to both the Shoutout and the Follow table.

So when a user logs in, I want to get all the people that the User is following. This i do by the following:

$following = Follow::whereUser_id($user->id)->get();


However, how would I now get the
Shoutouts
from the users in
$following
, in an order descending from the time the shoutout was created, so I can do a foreach($shoutouts as $shoutout) in my view?
The ideas is that the User can see the latest Shoutouts from the people that the user is following.

If i did something like:

$following = Follow::whereUser_id($user->id)->get();

foreach($following as $follow) {
$shoutouts = $shoutouts . Shoutout::whereUser_id($follow->user_id)->get();
}


I would get them in an incorrect order, as this would give me all the shoutouts of a specific user right after each other, but I would like to get them in descending order, so the user sees the latest shoutouts first.

All help appreciated.

Answer

You can try it by using join as:

Assuming you have a followed_user_id in your Follow table.

$shoutouts = Shoutout::join('follow', 'shoutout.user_id', '=', 'follow.followed_user_id')
                    ->where('follow.user_id', $user->id)
                    ->select(''shoutout.*'')
                    ->orderBy('shoutout.created_at', 'desc')
                    ->get()