CODEWITHSUNDEEP
phplaravellaravel-5eloquent
0w3n86
0w3n86

Reputation: 519

Laravel 5 filter on pivot

I have two tables Candidates & Qualifications with a joining pivot table called Results.

What I want to do is select all candidates for this centre and filter them by a value in the pivot table.

So select all candidates from centre 1 where the pivot “status” = “REGISTERED”

I have tried numerous approaches and am currently with the one below but nothing is working. 

$candidates = Candidate::where('batch_centre_id','=', $id)->with(array('qualification' => function($q)
{
  $q->wherePivot('status','=', 'REGISTERED');
}))->get();

Any advice would be much appreciated

Upvotes: 3

Views: 1826

Answers (1)

jedrzej.kurylo
jedrzej.kurylo

Reputation: 40899

In Eloquent wherePivot method can only be loaded on a relation. The way you're doing it, you're calling it on qualifications relation, therefore you're fetching all candidates and then, for each of them, you're loading qualifications that have status=REGISTERED. That's why you're getting all candidates.

You could work around it by using Eloquent's whereHas() method that let's you filter the base model you're trying to fetch by attributes of some relation. In your case the following should work:

$candidates = Candidate::where('batch_centre_id','=', $id)
  ->whereHas('qualifications', function($q) {
    $q->wherePivot('status','=', 'REGISTERED');
  })->with('qualifications')->get();

This way you'll get only candidates that have a qualification with status=REGISTERED and all their qualifications. If you want to additionally filter loaded qualifications by status, add the constraint like you did in your code:

$candidates = Candidate::where('batch_centre_id','=', $id)
  ->whereHas('qualifications', function($q) {
    $q->wherePivot('status','=', 'REGISTERED');
  })->with(array('qualifications', function($q) {
    $q->wherePivot('status','=', 'REGISTERED');
  })->get();

Upvotes: 1

Related Questions