CakePhp create list with valueField from another table

Question

I want to create list from one table, using contain with another tables and make keyField from first Table but valueField from another Table + key.

I have table CustomerNumbers (key => customer_number) -> Users -> Groups (value => name) Need to create list with all CustomerNumbers.customer_number as key and Groups.name plus key as value.

here is my query (also, I do not want make standard classic php foreach to create list)

$customerNumbers = $customerNumbersTable
    ->find('list', [
            'keyField' => 'customer_number',
            'valueField' => 'user.group.name' . ' ' . 'customer_number'
        ])
        ->group([
            'customer_number'
        ])
        ->contain('Users.Groups')
        ->where(['customer_number >' => 0]);

result shoud be like

(int) 11010080 => Frist group 11010080,
(int) 20000710 => Second group 20000710,
(int) 20001205 => Third group 20001205,

Also this is not duplicated question @drmonkeyninja because in your example (Question) you need to use virtual field option from single model (ex. Model Users and take First and Last name as one field). In my case I need virtual field from model Customer Numbers and another field from Model Groups).

Here is solution in pure php, but I hope so that there is CakePhp soultion too.

$getCustomerNumbers = $customerNumbersTable->find('all')
      ->group([
        'customer_number'
      ])
      ->contain('Users.Groups')
      ->hydrate(false);

    $customerNumbers = [];
    foreach($getCustomerNumbers as $key => $val):
      $customerNumbers[$val['customer_number']] = 
         $val['user']['group']['first_name'] .
         $val['user']['group']['last_name'] . 
         ' ('. $val['customer_number'].')';
    endforeach;

Presumably you have a one-to-many relationship with your Users and CustomerNumbers tables. In which case `contain` isn't going to run this as one SQL query, so this won't work. — drmonkeyninja, Apr 28 '17 at 11:39
**http://stackoverflow.com/questions/32999490/how-do-i-create-a-keyvalue-pair-by-combining-having-two-fields-in-cakephp-3** — ndm, Apr 28 '17 at 12:27
Possible duplicate of [How do I create a keyValue pair by combining/having two fields in CakePHP 3?](http://stackoverflow.com/questions/32999490/how-do-i-create-a-keyvalue-pair-by-combining-having-two-fields-in-cakephp-3) — drmonkeyninja, Apr 28 '17 at 13:54
@ndm it is not the same. In your examples I can use virtual property and get 2 or more columns from single model, but in my case I need virtual field from 2 models. First one is Groups (first, last name) and second one is CustomerNumber model (column customer_number) as result. — JohnWayne, May 02 '17 at 09:24
It doesn't matter from which association you require something, in the callback or result formatter functionality (both being invoked after the data has been read) you have access to all contained associations, and you can build whatever string you like. — ndm, May 02 '17 at 10:50

score 4 · Answer 1 · edited Dec 30 '19 at 08:24

My example to find distinct countries with name in table Adressess:

$countries = TableRegistry::get('Addresses')->find('list', [
    'keyField' => 'country_id',
    'valueField' => function ($row) {
        return $row->country->name;
    }                
])
->contain(['Countries'])->where(['contact_id IS NOT' => null])
->group(['country_id'])
->order(['Countries.name' => 'asc']);

Work great

CakePhp create list with valueField from another table

1 Answers1