Eleminate the record id which are in whereNotIn condition

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",
        }
      }
    ]
  },
]

undeportedmexican

3 years ago

Level 15

Your whereNotIn() method is actually querying that those ids are not present, from the Games table.

In order to accomplish what you're trying to get, you need to query "all the games, that have a Team id different from 23 and 24". When you think about that, the query you get is quite different:

$team_ids_to_exclude = $request->arr_team_ids;

Game::query()
				->with('gameTeams.teams')
				->whereHas('gameTeams', function($query) use ($team_ids_to_exclude){
						$query->whereNotIn('team_id', $team_ids_to_exclude);
				})
				->get();

This should probably do the trick.

johannk

3 years ago

Level 1

@undeportedmexican

Result still not correct, getting record 23, 24 id which should not come from game_teams table

SilenceBringer

3 years ago

Level 55

@johannk you should apply your condition to with https://laravel.com/docs/9.x/eloquent-relationships#constraining-eager-loads

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

if you also need to filter the games (if they contains results for teams from $request->arr_team_ids only) use withWhereHas https://laravel.com/docs/9.x/eloquent-relationships#constraining-eager-loads-with-relationship-existence

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

Also confused why your retationship on gameTeams called teams - it should be team, I think (gameTeams belongsTo one team)

johannk

3 years ago

Level 1

result still not correct,

"result with your code, also getting record 23, 24 id which should not come from game_teams table "

SilenceBringer

3 years ago

Level 55

@johannk maybe $request->arr_team_ids do not contains 23, 24? dump request data to see that you have correct name and value

mishajib

3 years ago

Level 1

Thanks

Please or to participate in this conversation.