CODEWITHSUNDEEP
phpmysqllaraveleloquent
sebbalex
sebbalex

Reputation: 397

Filter results by pivot data on laravel / eloquent

I need to filter my data that match a specified criteria, see the example below.

user

id name
1 john
2 jim

company

id name
1 Google
2 Stackoverflow

user_company

user_id company_id end_date note
1 2 31/12/2018 Left
1 1 31/12/2020 Fired
1 2 NULL Hired
2 1 NULL Hired

I just wanted to know who and for which company is working actually.

User.php

public function companies() {
  return $this
      ->belongsToMany('App\Company', 'user_company', 'user_id', 'company_id')
      ->as('company')
      ->withPivot(['end_date', 'note'])
}

my query

$users = User::with('companies')->select(['user.*']);

Already tried using wherePivotNull('end_date') either in model relationship and in controller query, no success.. I still miss something.

Upvotes: 1

Views: 121

Answers (1)

John Lobo
John Lobo

Reputation: 15319

I think using whereHas, we can achieve that

 $users = User::with('companies')
        ->whereHas('companies',function ($query){
            $query->whereNull('user_company.end_date');
        })
        ->get();

Upvotes: 4

Related Questions