Laravel 5.4: Advanced pivot table query issue

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:

• Manufacturing: completed
• Packaging: (not needed / not attached)
• Shipping: in progress

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'

from Newest questions tagged laravel-5 - Stack Overflow http://ift.tt/2lhTUx4
via IFTTT