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

图片转代码服务由CSDN问答提供 功能建议

我有两个模型: Order Department ,已加入 通过多对多的关系。 此关系上的数据透视表包含“状态”字段。 因此,特定订单可能类似于:

  • 制造:已完成
  • 包装:(不需要/未附加)
  • 发货:正在进行中

    在我的应用的用户界面中,我有每个部门的标签和状态复选框。 因此,API需要能够接受具有一个部门和多个可能状态的请求,并返回与给定部门的所选状态之一匹配的所有订单。

    示例查询: / api / orders?dep =制造& statuses = notStarted,inProgress

    这需要返回 的所有订单 < / strong>制造部门“未开始”或“进行中”(无论其他部门的状态如何)

    以下是我写的查询: \ n

      $ query = Order :: with(“departments”); 
     $ department = Request :: get('department'); 
     $ statuses = explode(“,”,Request :: get  ('statuses',“”)); 
     
    if(!empty($ department))
     {
     $ query-&gt; whereHas('departments',function($ q)use($ department)
      {
     $ q-&gt; where('name',$ department); 
    }); 
     if(count($ statuses)&gt; 0)
     {
     $ query-&gt; where(function(  $ q)使用($ department,$ status)
     {
     foreach($ status作为$ sta  tus)
     {
     $ q-&gt; orWhereHas('departments',function($ q)use($ department,$ status)
     {
     $ q-&gt; where('name',$ department)  - &gt; wherePivot('status',$ status); 
    } 
    } 
    }); 
    } 
    } 
     
    return $ query-&gt; paginate(15); 
        
     
     

    抛出错误:

      SQLSTATE [42S22]:找不到列:1054'where子句中的未知列'pivot'  '
       
     
     

    我的关系定义如下:

     公共职能部门()
     {
    返回 $ this-&gt; belongsToMany('App \ Models \ Department','order_statuses') - &gt; using('App \ Models \ OrderStatus') - &gt; withPivot('status') - &gt; withTimestamps(); 
      } 
       
     
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • dongranding3909 2017-04-26 13:27
    已采纳

    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);
            }
        }
    }
    
    打赏 评论
  • doucan1996 2017-02-15 05:05

    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');
    
    打赏 评论

相关推荐 更多相似问题