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
– drmonkeyninja, Commented Apr 28, 2017 at 11:39
Possible duplicate of How do I create a keyValue pair by combining/having two fields in CakePHP 3? — drmonkeyninja
– drmonkeyninja, Commented Apr 28, 2017 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
– JohnWayne, Commented May 2, 2017 at 9: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
– ndm, Commented May 2, 2017 at 10:50

Bird87 ZA · Accepted Answer · 2019-12-30 08:24:22Z

4

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

edited Dec 30, 2019 at 8:24

Bird87 ZA

2,3518 gold badges39 silver badges72 bronze badges

answered Apr 19, 2018 at 8:12

onk1

412 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

CakePhp create list with valueField from another table

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related