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 · Accepted Answer · 2018-05-11 11:18:54Z

3

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.

2 Comments

chopstik Over a year ago

Top answer, thank you. I like the idea of approacing the list from the other side of the association in the first instance and using a matcher. In your opinion would using a 'hasMany through' association on Site allow find('list') to operate as I was expecting?

ndm Over a year ago

@chopstik Not really, unless you'd use it like the 3rd variant from your question, ie query via the join table. When using contain() the ORM will always retrieve hasMany and belongsToMany associations in a separate query, and leave you with the associated records set in an array on the respective parent record.

Collectives™ on Stack Overflow

CakePHP list finder method not returning array as expected

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related