What´s the right way to do deep notMatching() association in CakePHP 3

That is the expected result, in your setup there are 3 titles per game group, and only one of the titles has a user assigned, meaning for all other titles the user will be NULL, eg there are 2 titles for Test A left for which (TitlesUsers.id) IS NULL will be true.

Your non-grouped results would look like this:

| Games.id | Games.name | Titles.id | Titles.game_id | TitlesUsers.id | TitlesUsers.title_id | TitlesUsers.user_id |
| -------- | ---------- | --------- | -------------- | -------------- | -------------------- | ------------------- |
| 1        | Test A     | 1         | 1              | 1              | 1                    | 2                   |
| 1        | Test A     | 2         | 1              | NULL           | NULL                 | NULL                |
| 1        | Test A     | 3         | 1              | NULL           | NULL                 | NULL                |
| 2        | Test B     | 4         | 2              | NULL           | NULL                 | NULL                |
| 2        | Test B     | 5         | 2              | NULL           | NULL                 | NULL                |
| 2        | Test B     | 6         | 2              | NULL           | NULL                 | NULL                |
| 3        | Test C     | 7         | 3              | NULL           | NULL                 | NULL                |
| 3        | Test C     | 8         | 3              | NULL           | NULL                 | NULL                |
| 3        | Test C     | 9         | 3              | NULL           | NULL                 | NULL                |

As can be seen, every game group has at least one row where TitlesUsers.id is NULL.

You either need to rethink your database schema and associations, or if you need your schema to be that way, change how you filter things, as notMatching() isn’t suited for it, for example exclude the games that you do not want by testing against the games for which at least one title from a specific user exists:

$excludedGamesQuery = $this->Games
    ->find()
    ->select(['Games.id']);
    ->matching('Titles.TitlesUsers', function ($q){
        return $q->where(['TitlesUsers.user_id' => 2]);
    })
    ->group(['Games.id']);

$gamesQuery = $this->Games
    ->find()
    ->where([
        'Games.id NOT IN' => $excludedGamesQuery
    ]);

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top