CakePHP4 - Paginate with association condition

1

I have Matches model, one Match has many Bets. Each User has 1 bet for 1 match, thus one User can have multiple Bets for multiple Matches.

Now I want to display the list of Matches and associated bets of the logged in user only.
I read the documentation for buidling quere function to work with Pagination, but some how it does not work for me.

public function index() {
        $user = $this->Authentication->getIdentity();
        $uid = $user->get('id');
        //$this->set('id', $uid);
                
        $this->paginate = ['contain' => ['TeamAs', 'TeamBs',
            'Bets' => function (Query $query) {
                return $query->select(['match_id','user_id', 'score_a', 'score_b'])
                            ->where(['Bets.user_id' => $uid]);
            }
        ]];
        
        $matches = $this->paginate($this->Matches);

        $this->set(compact('matches'));
    }

The above code does not work, it complains $uid is undefined .
If I replace the $uid with hardcoded number (e.g.: 281 for my user id), the code run without getting any Bet of mine (even though I placed several bets, and sure they are in database, they can be retrieve in Bets index list.)

I also tried add more input parameters to statement function like below:

'Bets' => function (Query $query, int $user_ID) {

but then debugger complain that function require exact 2 parameters while only 1 feeded.

Can someone explain the rule of using function(Query $query) here?
And how can I change my code to make it working?

Thank you very much.

2

you can get the user id with:

$uid=$this->request->getAttribute(‘identity’)->id;

I think this

$this->paginate = [‘contain’ => [‘TeamAs’, ‘TeamBs’,
‘Bets’ => function (Query $query) {
return $query->select([‘match_id’,‘user_id’, ‘score_a’, ‘score_b’])
->where([‘Bets.user_id’ => $uid]);
}
]];

won’t work, because the variable $uid is not available for your query.
You could try with inserting use ($uid) into your query:

$this->paginate = [‘contain’ => [‘TeamAs’, ‘TeamBs’,
‘Bets’ => function ($query) use ($uid) {
return $query->select([‘match_id’,‘user_id’, ‘score_a’, ‘score_b’])
->where([‘Bets.user_id’ => $uid]);
}
]];

3

Thank you very much, Dirk,
No debuging error with your approach, but the result set is not what expected: All matches are retrieved , each has 1 Bet object, but it is null. And I did double check database with match ID and my user ID, the bets are there.

How can I debug the SQL query prepared by $this->paginate = statement?

Any other suggestion? Thank you very much

4

If you have installed the debug kit, you could click on the Sql Log.

5

I do have DebugKit installed. The SQL Log showed that my Matches is retrieved correctly with limit 20 (paginator setting) together with Team A and Team B in 1 SQL statement.

All Bets are retrieved correctly in another statement with correct conditions of my user ID and 20 of matches ID.
However, the Bets are not attached to Matches as contained just like Team A and Team B.
Thus, debug kit show Variables only Matchese without any Bets.

Any suggestion ? Thank you very much.

6

Hi Dirk,

I found out that it actually not an issue with query, the DebugKit somehow does not show all nested levels of objects, hence I can not see my Bets object in Matches object.

If in Template file, I put debug($matches->first()) ; then I can see the 1st match with all my required contain objects.

That means your statement about contain function works perfectly:

‘Bets’ => function ($query) use ($uid) {

Thanks Dirk, problem solved