CODEWITHSUNDEEP
cakephp
JohnWayne
JohnWayne

Reputation: 663

CakePhp create list with valueField from another table

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;

Upvotes: 3

Views: 1781

Answers (1)

onk1
onk1

Reputation: 41

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

Upvotes: 4

Related Questions