Laravel 5.2 subquery - filter rows by polymorphic pivot table last row

Question

I've been stuck on this for a few days now and wondering if anyone can help.

DB tables: 'pages', 'statuses', 'status_assignments'

Page model:

protected $appends = ['status'];

public function statuses()
{
    return $this->morphToMany('App\Models\Status', 'statusable', 'status_assignments')->withPivot('id', 'assigned_by_user_id', 'locked')->withTimestamps();
}

public function getStatusAttribute()
{
    return $this->statuses()->orderBy('pivot_id', 'DESC')->first();
}

public function scopeWithStatus($query, $statuses)
{
    $query->with('statuses');

    if(is_array($statuses) && count($statuses) > 0)
    {
        $query->whereHas('statuses', function($q) use($statuses)
        {
            $cs = $q->orderBy('pivot_id', 'DESC')->first();
            foreach($statuses as $status) $q->where('id', $status)->where('pivot_id', $cs->pivot->id);
        });
    }

    return $query;
}

Status Model:

public function pages()
{
    return $this->morphedByMany('App\Models\Page', 'categorizable', 'category_assignments')->withTimestamps();
}

What I'm trying to do is retrieve a list of pages where the latest status assigned has id of ['array_of_status_ids'] ('$statuses' in scopeWithStatus)

$pages = Page::withStatus([1,2,3,4])->get();

Any ideas on this would be gratefully received! Thanks in advance, Kris.

Answer 1

First up, pages() relation looks like its using the wrong pivot table details.

'categorizable', 'category_assignments'

Should those be 'statusable', 'status_assignments'.

Sometimes I find it simpler to throw some raw SQL in the mix rather than write a complicated Query Builder statement.

Using a sub query to check the latest status entry for each page is contained in $statuses:

public function scopeWithStatuses($query, $statuses = [])
{
    $subQuery = '(select status_id from status_assignments'
        . ' where status_assignments.statusable_id = pages.id'
        . ' and status_assignments.statusable_type = "App\\\Test\\\Page"'
        . ' order by status_assignments.id desc limit 1)';

    $query->whereIn(\DB::raw($subQuery), (array) $statuses);
}