Patrick Patrick - 9 months ago 38
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.

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

As it is made i Laravel, the User has a
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
from the users in
, 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 Source

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)
                    ->orderBy('shoutout.created_at', 'desc')