Sultenhest's avatar

Querying a hasMany through a belongsToMany

Hi guys

I'm currently practicing my Laravel skills by building a Twitter clone and i'm still relatively new at building eloquent queries.

I'm stuck building the "feed", which should consists of the statuses of the other user profiles the given user is following.

This is what my models looks like:

User.php

public function statuses()
{
    return $this->hasMany(Status::class)->latest('updated_at');
}

public function following()
{
    return $this->belongsToMany(User::class, 'follows', 'follower_user_id', 'followed_user_id');
}

public function followers()
{
    return $this->belongsToMany(User::class, 'follows', 'followed_user_id', 'follower_user_id');
}

Status.php

public function user()
{
    return $this->belongsTo(User::class);
}

This is what the MySQL query looks like:

SELECT statuses.*
FROM follows
INNER JOIN statuses ON follows.followed_user_id = statuses.user_id
WHERE follower_user_id = 151
ORDER BY statuses.created_at DESC
LIMIT 15;

Where 151 is the current user id and the limit is a pseudo pagination.

My current attempt at eloquent looks like this

auth()->user()->following()->with('statuses')->orderBy('created_at', 'desc')->paginate(15);

It is querying the correct followings along with ALL of their statuses, and its ordering by user.created_at and not status.created_at.

What have i missed?

0 likes
6 replies
mstrauss's avatar

I believe you would have to write a subquery on the Eager Loaded relationship statuses if you want to use an orderBy clause on that. Otherwise the orderBy will be applied to the following relationship model context, which is User.

So something like:

        auth()->user()->following()->with('statuses')->where(function ($query) {
            $query->orderBy('created_at', 'desc');
        })->paginate(15);

Or possibly something like this may work:

  auth()->user()->following()->with('statuses')->orderBy('statuses.created_at', 'desc')->paginate(15);
bobbybouwmann's avatar
bobbybouwmann
6 years ago
Best Answer
Level 88

@mstrauss This way you only get the statuses of everyone that you're following. You won't have your own statuses. And if you grab your own statuses separately you will most of the time have more than 15 results, so the pagination won't make any sense anymore.

mstrauss's avatar

@BOBBYBOUWMANN - Good call, I was just using the original query, and not really thinking about the paginate thing.

Sultenhest's avatar

Hi @bobbybouwmann

I went along with your suggestion and wrote a DB query like this

$statuses = DB::table('follows')
            ->join('statuses', 'follows.followed_user_id', '=', 'statuses.user_id')
            ->join('users', 'statuses.user_id', '=', 'users.id')
            ->select('statuses.*', 'users.username', 'users.name')
            ->where('follows.follower_user_id', '=', auth()->id())
            ->latest()
            ->limit(15)
            ->get()
            ->toArray();

Which seems to return what i want.

In the meantime i've decided to expand the project and implement an activity feed, which logs statuses, likes, user updates and so on, and use that as my main feed instead of just statuses.

Please or to participate in this conversation.