I have two models: Order and Department, joined by a many-to-many relationship. The pivot table on this relationship contains a "status" field. So a particular order may look something like:
In the UI for my app I have a tab for each department and checkboxes for statuses. So the API needs to be able to take a request with one department and multiple possible statuses and return all orders which match one of the selected statuses for the given department.
Example query: /api/orders?dep=manufacturing&statuses=notStarted,inProgress
This needs to return all orders which are either "not started" or "in progress" for the manufacturing department (regardless of status in any other department)
Here is the query I wrote:
$query = Order::with("departments");
$department = Request::get('department');
$statuses = explode(",", Request::get('statuses', ""));
if (!empty($department))
{
$query->whereHas('departments', function ($q) use ($department)
{
$q->where('name', $department);
});
if (count($statuses) > 0)
{
$query->where(function ($q) use ($department, $statuses)
{
foreach ($statuses as $status)
{
$q->orWhereHas('departments', function ($q) use ($department, $status)
{
$q->where('name', $department)->wherePivot('status', $status);
}
}
});
}
}
return $query->paginate(15);
This is throwing the error:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'pivot' in 'where clause'
My relationship is defined as follows:
public function departments()
{
return $this->belongsToMany('App\Models\Department', 'order_statuses')->using('App\Models\OrderStatus')->withPivot('status')->withTimestamps();
}
I ultimately came up with the following solution to this:
if (!empty($departments)
{
if (count($statuses) > 0)
{
$query->whereHas('departments', function ($q) use ($department, $statuses)
{
$q->where('name', $department)->whereIn('order_statuses.status', $statuses);
}
} else {
$query->whereHas('departments', function ($q) use ($department)
{
$q->where('name', $department);
}
}
}
By default, only the model keys will be present on the pivot object. If your pivot table contains extra attributes, you must specify them when defining the relationship:
return $this->belongsToMany('App\Role')->withPivot('column1', 'column2');