The problem

Thats one confusing title, but I believe the following example will make this problem a bit easier to understand:

Say you have two models, User and Login:

class User extends Model
{
    public function logins()
    {
        return $this->hasMany('App\Login');
    }
}

So to get the last login, you could do something like:

public function lastLogin()
{
    return $this->hasOne('App\Login')->latest();
}

And that works pretty well. But… what if we wanted to get all Users who’s last Login was from a mobile device? Easy, right?

public function scopeUsingDevice($query, $type)
{
    $query->whereHas('lastLogin', function ($query) use ($type) {
        $query->where('device_type', $type);
    });
}

Unfortunately, it doesn’t work. It looks like it does, but in our results we’ll see mixed results:

>>> $users = User::usingDevice('desktop')->get();
>>> foreach ($users as $user) {echo $user->lastLogin->device_type . PHP_EOL;}
mobile
mobile
mobile
mobile
mobile
desktop
desktop
desktop
desktop
desktop
desktop
...

Subqueries attempt

Te Mad Scientist himself, Jonathan Reinink had that great article and presentation at Laracon US 2019 on Dynamic Relationships using subqueries that should solve this…

So we setup a dynamic relationship to get the last login using a subquery:

public function lastLogin()
{
    return $this->belongsTo(Login::class);
}

public function scopeWithLastLogin($query)
{
    $query->addSubSelect('last_login_id', Login::select('id')
        ->whereColumn('user_id', 'users.id')
        ->latest()
    )->with('lastLogin');
}

Note: if you didn’t read Jonathan’s article, the subquery is dynamically generating the last_login_id column for the belongsTo relationship.

Simple and elegant, let’s try out our device scope:

Users::withLastLogin()->usingDevice('mobile')->get();

Unfortunately, a QueryException will be thrown for Unknown column ‘users.last_login_id’.

Subqueries: Take 2

By reworking the previous attempt a bit (and with inspiration from a guy named Simon and Jonas Staudenmeir in a discussion on Stack Overflow), we can accomplish everything we’re after using a subquery:

public function scopeUsingDevice($query, $device)
{
    $query->whereHas('logins', function ($query) use ($device) {
        $query->where('id', function ($sub) {
            $sub->from('logins')
                ->selectRaw('max(id)')
                ->whereColumn('logins.user_id', 'users.id');
            })->where('device_type', $device);
    });
}

Which can be abstracted and scaled using a Query Builder macro (or two):

Builder::macro('latestRelation', function () {
    $where = $this->wheres[0];
    
    return $this->where('id', function ($sub) use ($where) {
        $sub->from($this->from)
        ->selectRaw('max(id)')
        ->whereColumn($where['first'], $where['second']);
    });
});

Builder::macro('whereLatest', function ($column, $value) {
    return $this->latestRelation()->where($column, $value);
});

Why split the macro? I thought being able to pass other where methods, such as whereBetween() or whereNotNull might be handy in the future.

Using the macros, we get a super clean and elegant result:

public function lastLogin()
{
    return $this->hasOne('App\Login')->latest();
}

public function scopeUsingDevice($query, $device)
{
    return $query->whereHas('logins', function ($query) use ($device) {
        $query->whereLatest('device_type', $device);
    });
}

And implementing this in your own projects couldn’t be easier:

composer require nullthoughts/laravel-latest-relation

Improving performance

While the subquery solution works well, I believe we can achieve better performance using an Intermediate MySQL View in Part 2 of this article.

(This was originally posted as one long article, however I afterwards felt it would be better to refactor it into two seperate ones: Part 1 focused on Subqueries, and Part 2 on Intermediate Tables using MySQL Views.)

Laravel Latest Relation Macros Package

  • Github
  • composer: composer require nullthoughts/laravel-latest-relation

Resources & Inspiration

Know of a better way to accomplish this?

I’d love to hear from you and collaborate on this. Reach out to me on Twitter: @jani_gyllenberg