How do I create a keyValue pair (display field) by combining/having two fields in CakePHP 3?

Question

I need help getting two values (for human friendly dropdown) in a key-value pair using find or get or anything else. I need help with the simplest method of doing this in CakePHP.

Here's my attempt:

1. in my controller

     $users = $this->LocationsUser->Users->find('list', [
       'limit' => 1,
       'keyField' => 'id',
       'valueField' => ['first_name', 'last_name']
     ])->where(['id' => $id]);
   

2. In my view

     echo $this->Form->input('user_id', [
           'options' => $users,
           'type' => 'select',
           'multiple' => false,
         ]);
   

3. The result on my drop-down:

   <option value="10">Fabian;Pankiers</option>
   

See I need a result without the semicolon ";". Now I can use javascript to remove the semicolon but then thats overkill. Is there a simple way to achieve this within CakePHP 3?

Answer 1

There are various ways to solve this, here's three of them:

Use a callback for the valueField

Use a callback and stitch the value together by using values from the results.

$query = $this->LocationsUser->Users
    ->find('list', [
        'valueField' => function ($row) {
            return $row['first_name'] . ' ' . $row['last_name'];
        }
    ])
    ->where(['id' => $id]);

See also

• Cookbook > Database Access & ORM > Retrieving Data & Results Sets > Finding Key/Value Pairs

Result formatters

Use a result formatter that stitches the values together, that's what the list finder does internally too, and basically the same as the above example, just more complicated.

$query = $this->LocationsUser->Users
    ->find()
    ->select(['id', 'first_name', 'last_name'])
    ->formatResults(function($results) {
        /* @var $results \Cake\Datasource\ResultSetInterface|\Cake\Collection\CollectionInterface */
        return $results->combine(
            'id',
            function($row) {
                return $row['first_name'] . ' ' . $row['last_name'];
            }
        );
    })
    ->where(['id' => $id]);

Combined with virtual properties

You could also use (re-)use virtual properties here, given for example a property name full_name defined in your User entity class:

protected function _getFullName()
{
    return $this->_properties['first_name'] . ' ' . $this->_properties['last_name'];
}

You could just return that in the formatter instead of manually combining the two fields again:

function($row) {
    return $row['full_name'];
}

The same could be done in the valueField example.

See also

• Cookbook > Database Access & ORM > Query Builder > Queries Are Collection Objects
• Cookbook > Database Access & ORM > Query Builder > Adding Calculated Fields
• Cookbook > Database Access & ORM > Entities > Creating Virtual Properties

Computed fields

Add a computed field and use that one for the valueField option

$query = $this->LocationsUser->Users
    ->find('list', [
        'keyField' => 'id',
        'valueField' => 'concatenated'
    ]);
$query
    ->select([
        'id',
        'concatenated' => $query->func()->concat([
            'first_name' => 'literal',
            ' ',
            'last_name' => 'literal'
        ])
    ])
    ->where(['id' => $id]);

See also

• Cookbook > Database Access & ORM > Query Builder > Using SQL Functions

Override the list finder

If you want your custom list to be always applied, you could override the list finder in your Users table class.

use Cake\ORM\Query;

// ...

public function findList(Query $query, array $options)
{
    return $query
        ->find()
        ->select(['id', 'first_name', 'last_name'])
        // etc ...
        ;
}

That way you can continue to use find('list').

See also

• Cookbook > Database Access & ORM > Retrieving Data & Results Sets > Custom Finder Methods