Laravel Eloquent Polymorphic Scope latest first item

Question

Good day,

I'm a bit stuck here with fetching latest item using Laravel scopes and Eloquent Polymorphic One-to-Many relationship.

Given:

I'm using latest version of Laravel 6.x.
I have two models: Website and Status.
Status model is reusable and can be used with other models.
Each website has multiple statuses.
Every time status is changed a new record is created in DB.
Active website status is the latest one in the DB.

Websites model:

class Website extends Model
{
    public function statuses()
    {
        return $this->morphMany(Statuses::class, 'stateable');
    }

    public function scopePending($query)
    {
        return $query->whereHas('statuses', function ($query) {
            $query->getByStatusCode('pending');
        });
    }
}

Statuses model:

class Statuses extends Model
{
    public function stateable()
    {
        return $this->morphTo();
    }

    public function scopeGetByStatusCode($query, $statusCode)
    {
        return $query->where('status_code', $statusCode)->latest()->limit(1);
    }
}

The problem is that when I call:

Website::pending()->get();

The pending() scope will return all websites that have ever got a pending status assigned to them, and not the websites that have currently active pending status (eg. latest status).

Here is the query that is returned with DB::getQueryLog()

select * from `websites` 
    where exists 
    (
        select * from `statuses` 
        where `websites`.`id` = `statuses`.`stateable_id` 
        and `statuses`.`stateable_type` = "App\\Models\\Website" 
        and `status_code` = 'pending' 
        order by `created_at` desc limit 1
    ) 
and `websites`.`deleted_at` is null

What is the right way of obtaining pending websites using scope with polymorphic one-to-many relation?

Similar issue is descried here: https://laracasts.com/discuss/channels/eloquent/polymorphic-relations-and-scope

Thanks.

The raw query is not quite right, it got to be websites.id instead of website.id. — Kevin Bui
Good catch, but it's just a typo since I have changed some wording there. The SQL is purely an example output. — Russell

Russell Russell · Accepted Answer · 2019-12-07T14:36:13

Okay, after doing some research, I have stumbled across this article: https://nullthoughts.com/development/2019/10/08/dynamic-scope-on-latest-relationship-in-laravel/

Solution turned out to be quite eloquent (sorry for bad pun):

protected function scopePending($query)
{
    return $query->whereHas('statuses', function ($query) {
        $query->where('id', function ($sub) {
            $sub->from('statuses')
                ->selectRaw('max(id)')
                ->whereColumn('statuses.stateable_id', 'websites.id');
         })->where('status_code', 'pending');
    });
}

Laravel Eloquent Polymorphic Scope latest first item

1 Answers