CakePHP list finder method not returning array as expected

Question

I'm using Cake's $table->find('list') finder method to retrieve an array of associated users [id => name]. Have used it in the past and its worked well. In this find I'm containing a belongsToMany table and retrieving [id => name].

In \App\Model\Table\SitesTable::initialize the ClientUsersWithAuthorities belongsToMany relationship is defined

$this->belongsToMany('ClientUsersWithAuthorities', [
  'className'        => 'AppUsers',
  'joinTable'        => 'sites_client_users',
  'foreignKey'       => 'site_id',
  'targetForeignKey' => 'user_id',
  'propertyName'     => 'client_users_with_authorities']);

In \App\Controller\ClientGroupsController::getClientgroupUsers

$siteClientUsers = $this->Sites->find('list', [
  'keyField'   => 'client_users_with_authorities.id',
  'valueField' => 'client_users_with_authorities.full_name'])
->where(['id' => $siteId])
->contain(['ClientUsersWithAuthorities'])
->toArray();

$siteClientUsers returns [0 => null] instead of [1234 => 'Client 1 name', 5678 => 'Client 2 name'] as expected. Both users exist in the join table.

Three other methods (below) return the array I'm looking for and which I'm expecting $this->Sites->find('list') to produce. The cookbook describes associated data can be listed. https://book.cakephp.org/3.0/en/orm/retrieving-data-and-resultsets.html#finding-key-value-pairs

So why isn't $this->Sites->find('list') producing the expected array?

mapReduce

$mapper = function ($data, $key, $mapReduce) {
if (!empty($data['client_users_with_authorities'])) {
    foreach ($data['client_users_with_authorities'] as $user) {
        $mapReduce->emit($user);
    }}};
$siteClientUsers = $this->Sites->find('list', [
    'keyField'   => 'id',
    'valueField' => 'full_name'])
->where(['id' => $siteId])
->contain(['ClientUsersWithAuthorities'])
->mapReduce($mapper)
->toArray();

Hash::combine

$siteClientUsers = $this->Sites->find('all')
->where(['id' => $siteId])
->contain(['ClientUsersWithAuthorities'])
->toArray();
$siteClientUsers  = Hash::combine($siteClientUsers,'{n}.client_users_with_authorities.{n}.id', '{n}.client_users_with_authorities.{n}.full_name');

Temp belongsTo relationship on the join table

$table = $this->getTableLocator()->get('SitesClientUsers');
$table->belongsTo('Users', [
'className'   => 'AppUsers',
'foreign_key' => 'user_id']);
$siteClientUsers =  $table->find('list', [
    'keyField' => 'Users.id',
    'valueField' => 'Users.full_name'
])
->select(['Users.id', 'Users.full_name'])
->where(['SitesClientUsers.site_id' => $siteId])
->contain(['Users'])
->toArray();

ndm ndm · Accepted Answer · 2018-05-11T11:18:54

The Cookbook says:

You can also create list data from associations that can be reached with joins

The important part being "that can be reached with joins", which isn't the case for belongsToMany associations. Given that list transformation happens on PHP level, the description should maybe focus on that instead, but it's still kinda valid as it stands.

client_users_with_authorities will be an array, ie possibly multiple records, so you cannot use it to populate a parent record - one Sites record represents one list entry. If you want to find a list of ClientUsersWithAuthorities that belongs to a specific site, then you can either use one of your other solutions (or something similar to that), or you could for example query from the other side of the association and use a matcher on Sites:

$query = $this->Sites->ClientUsersWithAuthorities
    ->find('list', [
        'keyField' => 'id',
        'valueField' => 'full_name'
    ])
    ->matching('Sites', function (\Cake\ORM\Query $query) use ($siteId) {
        return $query->where([
            'Sites.id' => $siteId
        ]);
    })
    ->group('ClientUsersWithAuthorities.id');

This would create joins based on Sites.id, so that you'd only retrieve the ClientUsersWithAuthorities that are associated with Sites where Sites.id matches $siteId.

CakePHP list finder method not returning array as expected

1 Answers