Eleminate the record id which are in whereNotIn condition-CodePudding

I am trying to get the result of games where records are not in team_id array.

from front end, sending array of team_id [ 23, 24 ], the result should not consider 23, 24 id from game_teams table

games table

| id | name    |

| 42 | game 1  | 
| 43 | game 2  |

teams table

| id | name    |

| 22 | team 1  | 
| 23 | team 2  | 
| 24 | team 3  |
| 25 | team 4  |
| 26 | team 5  |

game_teams table

| id | game_id |  team_id |

| 1  | 42      |  22      |
| 2  | 42      |  23      |
| 3  | 43      |  23      |
| 4  | 43      |  24      |
| 5  | 43      |  25      |
| 6  | 43      |  26      |

$results = Game::with(['gameTeams.teams'])->whereNotIn("team_id", $request->arr_team_ids)->get()->toArray();

with above code the result is not as "Expected result".

Expected result below, here you can see the record 23, 24 are not expected result which are in game_teams table :

[
  {
    "id": 42,    
    "name": "game 1",
    "game_teams": [
      {
        "id": 1,
        "game_id": 42,
        "team_id": 22,
        "teams": {
          "id": 22,
          "name": "team 1",
          
        }
      }
    ]
  },
    {
    "id": 43,    
    "name": "game 2",
    "game_teams": [
      {
        "id": 5,
        "game_id": 43,
        "team_id": 25,
        "teams": {
          "id": 25,
          "name": "team 4"
        }
      },
      {
        "id": 6,
        "game_id": 43,
        "team_id": 26,
        "teams": {
          "id": 26,
          "name": "team 5",
        }
      }
    ]
  },
]

CodePudding user response：

You should be able to use whereDoesntHave to query on the association. It'll use a subselect, but you could also look at using a manual join if this doesn't do the trick.

$teamIds = [23, 24];
Game::whereDoesntHave('gameTeams', fn ($query) => $query->whereIn($teamIds))->get()

CodePudding user response：

Use Constraining Eager Loading like this

$team_ids = $request->arr_team_ids;

$users = Game::with(['gameTeams.teams' => function ($query) {
    $query->whereNotIn('team_id', $team_ids);
}])->get()->toArray();

Docs: https://laravel.com/docs/5.3/eloquent-relationships#constraining-eager-loads