# CakePHP4 - Paginate with association condition

**URL:** <https://discourse.cakephp.org/t/cakephp4-paginate-with-association-condition/9444>\
**Category:** Uncategorized\
**Created:** [June 16, 2021, 10:44am UTC](https://discourse.cakephp.org/t/cakephp4-paginate-with-association-condition/9444 "2021-06-16T10:44:35Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![general2000vn](https://avatars.discourse-cdn.com/v4/letter/g/a87d85/32.png) [@general2000vn](https://discourse.cakephp.org/u/general2000vn)\
**Post date:** [June 16, 2021, 10:44am UTC](https://discourse.cakephp.org/t/cakephp4-paginate-with-association-condition/9444/1 "2021-06-16T10:44:35Z")

</div>

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.

```auto
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:

```auto
'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.

---

<div class="post-metadata">

**Author:** ![dirk](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/dirk/32/2174_2.png) [@dirk](https://discourse.cakephp.org/u/dirk)\
**Post date:** [June 16, 2021, 11:05am UTC](https://discourse.cakephp.org/t/cakephp4-paginate-with-association-condition/9444/2 "2021-06-16T11:05:39Z")

</div>

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]);  
> }  
> ]];

---

<div class="post-metadata">

**Author:** ![general2000vn](https://avatars.discourse-cdn.com/v4/letter/g/a87d85/32.png) [@general2000vn](https://discourse.cakephp.org/u/general2000vn)\
**Post date:** [June 16, 2021, 12:33pm UTC](https://discourse.cakephp.org/t/cakephp4-paginate-with-association-condition/9444/3 "2021-06-16T12:33:58Z")

</div>

> [@dirk](#):
>
> ‘Bets’ =\> function ($query) use ($uid) {  
> return $query-\>select([‘match\_id’,‘user\_id’, ‘score\_a’, ‘score\_b’])  
> -\>where([‘Bets.user\_id’ =\> $uid]);  
> }  
> ]];

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

---

<div class="post-metadata">

**Author:** ![dirk](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/dirk/32/2174_2.png) [@dirk](https://discourse.cakephp.org/u/dirk)\
**Post date:** [June 16, 2021, 1:08pm UTC](https://discourse.cakephp.org/t/cakephp4-paginate-with-association-condition/9444/4 "2021-06-16T13:08:36Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![general2000vn](https://avatars.discourse-cdn.com/v4/letter/g/a87d85/32.png) [@general2000vn](https://discourse.cakephp.org/u/general2000vn)\
**Post date:** [June 16, 2021, 1:30pm UTC](https://discourse.cakephp.org/t/cakephp4-paginate-with-association-condition/9444/5 "2021-06-16T13:30:26Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![general2000vn](https://avatars.discourse-cdn.com/v4/letter/g/a87d85/32.png) [@general2000vn](https://discourse.cakephp.org/u/general2000vn)\
**Post date:** [June 17, 2021, 5:27am UTC](https://discourse.cakephp.org/t/cakephp4-paginate-with-association-condition/9444/6 "2021-06-17T05:27:19Z")

</div>

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:

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

```

Thanks Dirk, problem solved