Laravel: Has Many Through Pivot… elegantly

Laravel Relationships pretty much cover everything that a model can access. We have One-To-One, One-To-Many, Many to Many, Polymorphic relations, etc. But there is one relation that is not covered because it’s very niche: Has Many Through Pivot.

Has Many Through Pivot means: accessing a distant “one to many” relationship though another relationship that uses a Pivot table (even a polymorphic one) to connect.

And you can do it too:

Without downloading composer packages with god knows what,
Without creating endless classes that overrides Eloquent Relationships like they were close friends,
Without having to write raw DB calls and joins like if you would understand what you did 3 months from now.

And there is only one way to do it, gracefully.

Let’s make an scenario were we can use this

Let’s forget the Adaptive Bitrate Streaming for a moment, shall we?

I have a site where people can make podcasts. They upload the Audio Files for their Podcast, and let the Users listen to them by a Subscription. The catch is quality: only users who pay for the podcast can hear the high quality version of it.

Let’s have this clear: we have one Podcast, and each of them have many Audio Files. While a Podcast holds the information about itself, like hosts, topics, duration and splash image, Audio Files reference the different audio qualities available for the podcast, like 64 kbps MP3 and 192 kbps. So, in this case, we would have 2 Audio Files for one Podcast.

With this approach, a User who pays can access to files with greater quality than those who doesn’t, who will be stuck with 64kbps audio like 1998.

How we can attach the User to a Podcast? A Subscription Pivot will help us with both, where we will also save if the user has paid for the Podcast with a nice has_paid boolean column. It’s basically a Many-To-Many relation. There are many Users, and many Podcasts.

The relation access would look like this:

I suck at any form of drawing. Art has my weakest grades. Sorry.

Hello app, what is my latest high quality Audio File? Uh?

At first glance we know we can’t access directly to the Audio Files from the User model, thus we have go through querying the Podcasts first and then the Audio Files.

From the User, but grouped by podcast, which is not what we are looking.

$user->podcasts()
    ->with([
        'audioFiles' => function ($query) {
            return $query->orderBy('created_at', 'desc');
        }
    ])
    ->get();

Naively, we may use something like this to get all the Audio Files.

AudioFiles::whereHas('podcast', function($query) use ($user) {
    return $query->whereHas('user', function($query) use ($user) {
        return $query->where('user_id', $user->id)
            ->where('has_paid', true);
    });
})->orderBy('created_at', 'desc')->get();

How we can simplify this? We only want the audio files, not the whole podcast and things! It’s very, very easy. If you understood the purpose of highlighting the IDs in the image, then everything will be more clear than water in the next steps.

First step: Make a Subscription Pivot

Doing this allows to access directly to the pivot table information without the need of raw DB calls. You can use Artisan to make it, or just write it down.

If your Pivot wants to connect a morphable model, this class should extend MorphPivot instead of the common Pivot. A polymorphic pivot declares almost the same.

Second Step: Prepare your Podcast Model

Here we need to tell the Podcast Model that we will use our newly created Subscription Pivot model to connect it to the User model.

// App\Podcast.phppublic function users()
{
    return $this->belongsToMany('App\Users')
        ->using('App\Pivots\Subscription');
}

Third Step: Prepare your User Model

Again, the same concept for the User Model.

// App\User.phppublic function podcasts()
{
    return $this->hasMany('App\Podcast')
        ->using('App\Pivots\Subscription');
}

Last Step: Add the magic powder

Don’t close our User model in your editor just yet! Now we have to tell it to access the Audio Files using a Has Many Through relationship, but not from Podcast model, but rather our Subscription Pivot model.

With that magic, you can access to the Audio Files from the User model with no problems at all, no matter how many Audio Files it may have as listenable. As a bonus, we aren’t creating any new class hard to maintain, only what Laravel gives us.

Optional: Define the inverse of the relationship

To do the same on the Audio File, you can tell the model to use the Subscription Pivot model to connect:

public function users()
{
    return $this->hasManyThrough(
        'App\Users'
        'App\Pivots\Subscription',
        'podcast_id',
        'user_id',
        'id',
        'podcast_id'
    );
}

Once we do this, we can access how many users can access these files by their subscription. We are done, and now we can do things like this:

/* Get the latest Audio File the user is Subscribed, in HQ */
$user->audioFiles()
    ->where('quality', '>=', 192)
    ->orderBy('created_at', 'desc')
    ->first();

How can I filter by a Pivot column?

Let’s say someone created a paid-only Podcast. No problem.

To filter the Audio Files behind a paid Podcast for the free users, we can simply call wherePivot() method on the query builder.

/* Get the latest Audio File this Free User can listen to */
$user->audioFiles()
    ->wherePivot('has_paid', false)
    ->orderBy('created_at', 'desc')
    ->first();