dongxuan1314 2017-02-15 03:31
浏览 121
已采纳

Laravel 5.4:高级数据透视表查询问题

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'

My relationship is defined as follows:

public function departments()
{
    return $this->belongsToMany('App\Models\Department', 'order_statuses')->using('App\Models\OrderStatus')->withPivot('status')->withTimestamps();
}
  • 写回答

2条回答

      报告相同问题?

      相关推荐 更多相似问题

      悬赏问题

      • ¥20 求无向连通网的所有不同构的最小生成树
      • ¥15 实现0.5秒间隔的单向流水灯
      • ¥30 网络操作系统问题求解
      • ¥30 blob下载word后内容不对
      • ¥15 求用C语言数组编程(需要完整且正确的程序)
      • ¥15 前端和swagger调导出接口乱码
      • ¥20 有没有ftps服务器可以使用公钥登录的?
      • ¥20 Ffmpeg代码层面如何实现视频陪速播放
      • ¥15 用python定义函数实现功能使用列表中动漫信息
      • ¥15 unity 使用 Vuforia 导入Model target 成功后database下拉菜单不显示导入的内容