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.
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);
}