php Laravel 5 问题与 wherePivot

Laravel 5 issue with wherePivot

提问by whoacowboy

I am working with Laravel 5 and I am having issue getting ->wherePivot()to work on a Many-to-Many relationship. When I dd()the SQL it looks like Eloquent is looking for records in the pivot table with a `pose_state`.`pose_id` is null`.

我正在使用 Laravel 5，但在->wherePivot()处理多对多关系时遇到问题。当我dd()使用 SQL 时，看起来 Eloquent 正在数据透视表中查找带有 `pose_state`.`pose_id` 为 null` 的记录。

I am hoping it is a simple error and not a bug. Any ideas are appreciated.

我希望这是一个简单的错误而不是错误。任何想法表示赞赏。

Database Structure

数据库结构

pose

姿势

id
name
type

state

状态

id
name
machine_name

pose_state

姿势状态

pose_id
state_id
status

Models

楷模

Pose

姿势

<?php namespace App;
use DB;
use App\State;
use Illuminate\Database\Eloquent\Model;

class Pose extends Model {

  public function states()
  {
      return $this->belongsToMany('App\State')
                  ->withPivot('status_id')
                  ->withTimestamps();
  }
  public function scopeWithPendingReviews()
  {
      return $this->states()
                  ->wherePivot('status_id',10);
  }
}

State

状态

<?php namespace App;

use Illuminate\Database\Eloquent\Model;

class State extends Model {

    public function poses()
    {
      return $this->belongsToMany('Pose')
                  ->withPivot('status_id')
                  ->withTimestamps();
    }

}

PosesController function

姿势控制器功能

public function listPosesForReview(){
    $poses = Pose::withPendingReviews()->get();
    dd($poses->toArray() );
}

SQL

SQL

select 
  `states`.*, `pose_state`.`pose_id` as `pivot_pose_id`,
  `pose_state`.`state_id` as `pivot_state_id`, 
  `pose_state`.`status_id` as `pivot_status_id`, 
  `pose_state`.`created_at` as `pivot_created_at`, 
  `pose_state`.`updated_at` as `pivot_updated_at` 
from 
  `states` inner join `pose_state` on `states`.`id` = `pose_state`.`state_id` 
where 
  `pose_state`.`pose_id` is null and `pose_state`.`status_id` = ?

EDIT

编辑

When I updated my code to removing the scope it worked. Thanks @Deefour for putting me on the right path! Maybe scope has something else to that I am missing.

当我更新我的代码以删除它工作的范围时。感谢@Deefour 让我走上正确的道路！也许范围还有其他我想念的东西。

public function pendingReviews()
{
  return $this->states()
              ->wherePivot('status_id','=', 10);
}

YET ANOTHER EDIT

另一个编辑

I finally got this to work. The solution above was giving me duplicate entries. No idea why this works, but it does, so I will stick with it.

我终于得到了这个工作。上面的解决方案给了我重复的条目。不知道为什么这行得通，但确实如此，所以我会坚持下去。

public function scopeWithStatusCode($query, $tag)
{
  $query->with(['states' => function($q) use ($tag)
            {
              $q->wherePivot('status_id','=', $tag);
            }])
        ->whereHas('states',function($q) use ($tag)
            {
              $q->where('status_id', $tag);
            });
}

采纳答案by deefour

I think your implementation of scopeWithPendingReviews()is an abuse of the intended use of scopes.

我认为您的实现scopeWithPendingReviews()是对范围的预期用途的滥用。

That aside, I believe you're not using wherePivot()properly. According to the source, the method signature is

除此之外，我相信你没有wherePivot()正确使用。根据消息来源，方法签名是

public function wherePivot($column, $operator = null, $value = null, $boolean = 'and')

but you're treating it as

但你把它当作

public function wherePivot($column, $value = null, $boolean = 'and')

This means

这意味着

->wherePivot('status_id',10)

should be

应该

->wherePivot('status_id', '=', 10)

---

---

Responding to Comments

回复评论

A scope should be thought of as a reusable set of conditions to append to an existing query, even if that query is simply

范围应该被认为是一组可重用的条件来附加到现有查询，即使该查询只是

SomeModel::newQuery()

The idea is that a pre-existing query would be further refined (read: 'scoped') by the conditions within the scope method, not to generate a new query, and definitely not to generate a new query based on an associated model.

这个想法是，预先存在的查询将通过 scope 方法中的条件进一步细化（阅读：'scoped'），而不是生成新查询，并且绝对不会基于关联模型生成新查询。

By default, the first and only argument passed to a scope method is the query builder instance itself.

默认情况下，传递给作用域方法的第一个也是唯一的参数是查询构建器实例本身。

Your scope implementation on your Posemodel was really a query against the statestable as soon as you did this

执行此操作后，您在Pose模型上的范围实现实际上是对states表的查询

$this->states()

This is why your SQL appears as it does. It's also a clear indicator you're misusing scopes. A scope might instead look like this

这就是您的 SQL 出现的原因。这也是您滥用范围的明确指示。一个范围可能看起来像这样

public function scopeWithPendingReviews($query) {
  $query->join('pose_state', 'poses.id', '=', 'pose_state.pose.id')
        ->where('status_id', 10);
}

Unlike your new pendingReviews()method which is returning a query based on the Statemodel, this scope will refine a query on the Posemodel.

与pendingReviews()基于State模型返回查询的新方法不同，此范围将优化对Pose模型的查询。

Nowyou can use your scope as you originally intended.

现在，您可以按照最初的预期使用示波器。

$poses = Pose::withPendingReviews();

which could be translated into the more verbose

可以翻译成更详细的

$poses = Pose::newQuery()->withPendingReviews();

Notice also the scope above doesn't return a value. It's accepting the existing query builder object and adding onto it.

还要注意上面的范围不返回值。它接受现有的查询构建器对象并添加到它上面。

The other answer to this question is filled with misinformation.

这个问题的另一个答案充满了错误信息。

1. You cannot use wherePivot()as is claims.
2. Your use of withTimestamps()is not at all related to your problem
3. You don't have to do any "custom work" to get timestamps working. Adding the withTimestamps()call as you did is all that is needed. Just make sure you have a created_atand updated_atcolumn in your join table.

1. 您不能wherePivot()按原样使用声明。
2. 您的使用withTimestamps()与您的问题完全无关
3. 您无需执行任何“自定义工作”即可使时间戳正常工作。只withTimestamps()需要像您一样添加呼叫即可。只要确保您的连接表中有一个created_at和updated_at列。

回答by Scopey

I think that your implementation of scopes is fine, the problem I see is just a typo. Your schema shows that the field is called statusbut your where condition is referring to a status_id

我认为您对范围的实现很好，我看到的问题只是一个错字。您的架构显示该字段已被调用，status但您的 where 条件指的是status_id

Try:

尝试：

->wherePivot('status', 10);

Also, the withTimestamps()method is causing issues. You don't have timestamps in your schema for the pivot (as I can see) so you shouldn't be putting these in the your relation definitions as it's trying to fetch the timestamps relating to when the relation was created/updated. You can do this if you set up your pivot table schema to have the timestamp fields, but I think you'll have to do some custom work to get the timestamps to save properly.

此外，该withTimestamps()方法会导致问题。您的架构中没有用于数据透视的时间戳（如我所见），因此您不应该将这些放在关系定义中，因为它试图获取与创建/更新关系时相关的时间戳。如果您将数据透视表架构设置为具有时间戳字段，则可以执行此操作，但我认为您必须进行一些自定义工作才能正确保存时间戳。

回答by osroflo

This worked for me (Laravel 5.3):

这对我有用（Laravel 5.3）：

$task = App\Models\PricingTask::find(1);
$task->products()->wherePivot('taggable_type', 'product')->get();

回答by Andrew

You can also have this problem (return no results) if the column you are using in wherePivothasn't been added to withPivot.

如果您使用的列wherePivot尚未添加到withPivot.