Laravel Eloquent：选择相关表有x的位置，但从相关表中排除某些行

Question

Im trying to return a collection from the database that houses a 'User Group' as well as its related users. However, I want the selection criteria to be based on whether or not a user in the related table is trained or not. If this is the case, I want that row/entry to be EXCLUDED from the final collection.

Essentially, I want to:

Return all groups of type 'GreenGroup' where at least one related employee is untrained, and show only untrained employees in the table.

Such that it would return an object like this:

0: {
id: 2,
group: GreenGroup,
size: 4,
employees: {
   0: {name: 'Jane', isTrained: 0},
   1: {name: 'Jeremy', isTrained: 0}
  }
}

But right now, it is finding the GreenGroup, but returning ALL users in that group. I am looking for a way to exclude them in the query builder. So far, this is what I have:

$results = Group::with(['employees'])->where('group', '=', 'GreenGroup')->whereHas('employees', function($query){
      $query->where('isTrained', '=', '0');
    })->get();

  return json_encode($results);

Is there a way to exclude relational table entries in the querybuilder? Or am I forced (when I loop through these and display them on my site) to write logic that checks if they're trained or not?

Answer 1

You need to change your query like I mentioned below:

 $results = Group::with(['employees' => function($query){
       $query->where('isTrained', '=', '0');
     }])->where('group', '=', 'GreenGroup')->get();

   return json_encode($results);

The query which you mentioned will return always all the members because you set condition in whereHas not in with.

The query you wrote is mainly used to get all the groups with nontrained members ( for this you need to remove where('group', '=', 'GreenGroup') ) but you want something opposite, so you need to set condition in With.