Laravel 5 issue with wherePivot

Question

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`.

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

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.

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

Answer 1

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

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.

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

Your scope implementation on your Pose model was really a query against the states table as soon as you did this

$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

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 State model, this scope will refine a query on the Pose model.

Now you 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_at and updated_at column in your join table.

Answer 2

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 status but your where condition is referring to a 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.

Answer 3

This worked for me (Laravel 5.3):

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