Cakephp3- unable to show count of values

Need Help
1

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’,‘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.

2 
'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

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