Finding items using pivot table

I am having a bit of trouble with Eloquent.

I have three models:

User which has many jobs:

public function jobs()
    {
        return $this->hasMany('App\Job')->withTimestamps();
    }

Job which has many stages:

 protected $table = 'jobs';

  protected $fillable =  [
    'address',
    'user_id',
  ];

  public function users()
    {
        return $this->belongsTo('App\User')->withTimestamps();
    }

   public function stages()
    {
        return $this->belongsToMany('App\Stage')->withTimestamps()->withPivot('status','order');
    }

Stage

  protected $table = 'stages';

  protected $fillable =  [
    'title',
    'description',
  ];

  public function faqs()
    {
        return $this->belongsToMany('App\Faq')->withTimestamps();
    }

  public function jobs()
    {
        return $this->belongsToMany('App\Job')->withPivot('status','order');
    }

On the job_stage pivot table I have two extra columns, order and status. I would like to find all stages belonging to a user, where the status is 'ready' and order by the 'order' column. As a newbie I am struggling with the three table join and filtering based on the pivot table fields - any pointers would be appreciated.

Thanks,

sid405

10 years ago

Level 27

@alex_hill Something like this although it's untested should point you the right way

return User::whereId($user_id)->whereHas('jobs.stages' , function($query) {
    $query->where('status', 'ready')->orderBy('order');
})->get();

alex_hill

10 years ago

Level 3

Thanks @sid405 .

I've had a bit of a play, and not quite getting it to work. The code below gives me a user, but no stages etc. I think I might call it a night and try again with fresh eyes tomorrow.

$stage = User::whereId(1)->whereHas('jobs.stages' ,
                                         function($query) {
        $query->where('status', '=','ready')->orderBy('order');
      })->get();

As someone who works with raw SQL queries every day, this whole ORM thing is doing my head in.

sid405

10 years ago

Level 27

@alex_hill Sorry man, i overlooked something

return User::with('jobs.stages)->whereId($user_id)->whereHas('jobs.stages' , function($query) {
    $query->where('status', 'ready')->orderBy('order');
})->get();

sid405

10 years ago

Level 27

I'm kinda doubtful about that one but check it out. If not we modd it again

Please or to participate in this conversation.