# Cakephp3- unable to show count of values

**URL:** <https://discourse.cakephp.org/t/cakephp3-unable-to-show-count-of-values/2591>\
**Category:** Need Help\
**Created:** [May 18, 2017, 10:31am UTC](https://discourse.cakephp.org/t/cakephp3-unable-to-show-count-of-values/2591 "2017-05-18T10:31:02Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![themarsbeing](https://avatars.discourse-cdn.com/v4/letter/t/85e7bf/32.png) [@themarsbeing](https://discourse.cakephp.org/u/themarsbeing)\
**Post date:** [May 18, 2017, 10:31am UTC](https://discourse.cakephp.org/t/cakephp3-unable-to-show-count-of-values/2591/1 "2017-05-18T10:31:02Z")

</div>

I am trying to show number of users present in a room. But when I try to do that, I get error.

This is the **relationship**.

Rooms has many users.  
Rooms belong to groups.

```
public function initialize(array $config)
{
    parent::initialize($config);

    $this->table('rooms');
    $this->displayField('name');
    $this->primaryKey('id');

    $this->belongsTo('Groups', [
        'foreignKey' => 'group_id'
    ]);
    $this->hasMany('Users', [
        'foreignKey' => 'room_id'
    ]);
}

```

To achieve this, I wrote a **controller** function which looks like this:

```
public function index()
{
   $this->loadComponent('Prg');
   $this->Prg->commonProcess();
   $params = $this->Prg->parsedParams();
   $this->paginate = [
    'limit' => 25,
    'order' => ['id' => 'ASC'], 
    'finder' => ['RoomsList' =>['filter'=> $params]],
    'sortWhitelist' => ['Rooms.id','Rooms.name'],
    'extraOptions' =>['params' => $params]
    ];

   $rooms = $this->paginate($this->Rooms);
   $this->set(compact('rooms'));
   $this->set('_serialize', ['rooms']);
}

```

This function paginates the list of rooms available. This is working fine but now when I try to show the count of users in each room, it throws error.

I modified my **model** function like this:

```
public function findRoomsList(Query $query, array $options)
{ 
    $query->select(['id','name','modified','created'])
    ->contain([
                    'Groups'=> function ($query) {
                        return $query->select(['id','name']);
                    },
                    'Users' => function($query){
                        return $query->select(['status','full_name']);
                        //want to fetch total number of users
                    }
            ]);

    echo $query;
    return $query;
}

```

I am trying to show number of users present in a room. But when I try to do that, I get error.

This is the relationship.

Rooms has many users.  
Rooms belong to groups.

public function initialize(array $config)  
{  
parent::initialize($config);

```
$this->table('rooms');
$this->displayField('name');
$this->primaryKey('id');

$this->belongsTo('Groups', [
    'foreignKey' => 'group_id'
]);
$this->hasMany('Users', [
    'foreignKey' => 'room_id'
]);

```

}  
To achieve this, I wrote a controller function which looks like this:

public function index()  
{  
$this-\>loadComponent(‘Prg’);  
$this-\>Prg-\>commonProcess();  
$params = $this-\>Prg-\>parsedParams();  
$this-\>paginate = [  
‘limit’ =\> 25,  
‘order’ =\> [‘id’ =\> ‘ASC’],  
‘finder’ =\> [‘RoomsList’ =\>[‘filter’=\> $params]],  
‘sortWhitelist’ =\> [‘[Rooms.id](http://Rooms.id)’,‘[Rooms.name](http://Rooms.name)’],  
‘extraOptions’ =\>[‘params’ =\> $params]  
];

$rooms = $this-\>paginate($this-\>Rooms);  
$this-\>set(compact(‘rooms’));  
$this-\>set(’\_serialize’, [‘rooms’]);  
}  
This function paginates the list of rooms available. This is working fine but now when I try to show the count of users in each room, it throws error.

I modified my model function like this:

public function findRoomsList(Query $query, array $options)  
{  
$query-\>select([‘id’,‘name’,‘modified’,‘created’])  
-\>contain([  
‘Groups’=\> function ($query) {  
return $query-\>select([‘id’,‘name’]);  
},  
‘Users’ =\> function($query){  
return $query-\>select([‘status’,‘full\_name’]);  
//want to fetch total number of users  
}  
]);

```
echo $query;
return $query;

```

}  
Now I’m getting this error:

`You are required to select the "Users.room_id" field(s)`

Can anyone tell me the mistakes I made here? I’m new to CakePHP3.

---

<div class="post-metadata">

**Author:** ![Diego](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/diego/32/296_2.png) [@Diego](https://discourse.cakephp.org/u/Diego)\
**Post date:** [May 18, 2017, 11:07am UTC](https://discourse.cakephp.org/t/cakephp3-unable-to-show-count-of-values/2591/2 "2017-05-18T11:07:56Z")

</div>

> [@themarsbeing](#):
>
> You are required to select the “Users.room\_id” field(s)

```
'Users' => function($query){
    return $query->select(['room_id', 'status','full_name']);
    //want to fetch total number of users
}

```

if u dont need user data just the total you should check this:  
[https://book.cakephp.org/3.0/en/orm/query-builder.html#using-sql-functions](https://book.cakephp.org/3.0/en/orm/query-builder.html#using-sql-functions)

`$query->select(['count' => $query->func()->count('*')]);`