[FIXED] JSON array as select options and null displayname

Issue

I cannot get a multi select to populate properly.

1. Even though I'm specifying the keyField and valueField, I'm getting JSON as option values
2. name is null and shouldn't be

Example:

<option value="0">{
    "id": 8263,
    "name": null,
    "sort": "1"
}</option>

clients MySQL table columns: id, name_en, name_fr...

Client Entity:

protected $_virtual = [
            'name',
        ];

protected function _getName()
{
    if (Configure::read('wetkit.lang') == 'fr'){
        return $this->get('name_fr');
    } else {
        return $this->get('name_en');
    }
}

ClientsTable table:

$this->setDisplayField('name');

Controller:

//build query to highlight selected clients
$selected_clients = $this->Clients->find()
->select(['id' => 'id','name' => 'name_'.$this->lang,'sort'=>1])
->where(['id in' => $user_clients]);

$clients = $this->Clients->find()
->select(['id' => 'id','name' => 'name_'.$this->lang,'sort'=>2])
->where(['id not in' => $user_clients]);

$clients->unionAll($selected_clients)->epilog('order by sort asc, name asc');

SQL:

(
  SELECT 
    id AS `id`, 
    name_en AS `name`, 
    2 AS `sort` 
  FROM 
    clients Clients 
  WHERE 
    id not in (8263)
) 
UNION ALL 
  (
    SELECT 
      id AS `id`, 
      name_en AS `name`, 
      1 AS `sort` 
    FROM 
      clients Clients 
    WHERE 
      id in (8263)
  ) 
order by 
  sort asc, 
  name asc

In the view:

<?= $this->Form->control('clients._ids', 
                ['options' => $clients, 
                'label'=>__('Client'), 
                'keyField' => 'id', 
                'valueField' => 'name',
                'values' => $user_clients]) ?>

Solution:

This seems to work. I did have to figure out the appropriate sort to reflect the ORM's field name (ie. Client__name_en). I also wrapped this up in a component and can use app wide using other models.

//build query to highlight selected clients
$selected_clients = $this->Clients->find()
->select(['id','name_en','name_fr','sort'=>1])
->where(['id in' => $user_clients]);

$clients = $this->Clients->find()
->select(['id','name_en','name_fr','sort'=>2])
->where(['id not in' => $user_clients]);

$clients->unionAll($selected_clients)
->epilog('order by sort, Clients__name_'.$this->lang);

$clients = $clients->combine('id','name_'.$this->lang);

Solution

The keyField and valueField options belong to the list finder, not to form controls.

$clients
    ->unionAll($selected_clients)
    ->find('list', [
        'keyField' => 'id', 
        'valueField' => 'name',
    ])
    // ...

Looks a bit weird, but finders can be chained, so to speak. Alternatively you can use Collection::combine() directly, that is what is applied automatically when using the list finder:

'options' => $clients->combine('id', 'name'),
// ...

And name is null because you do not select the name_en or name_fr fields that your entity tries to lookup. Selecting a value as name will not circumvent the entity's accessor method, it will always run when you try to access the name field on the entity, no matter if a value for that field exists.

The accessor method will receive the value of the possibly existing field as its first argument, so you might be able to use that, something along the lines of:

protected function _getName($name)
{
    if ($name !== null) {
        return $name;
    }

    // ...
}

However this is all somewhat finicky and error prone, ending up with entity behavior bound to application state doesn't bode well for the future. Maybe you should give CakePHP's built-in translation functionality a try.

See also

• Cookbook > Database Access & ORM > Retrieving Data & Results Sets > Finding Key/Value Pairs
• Cookbook > Collections > combine()
• Cookbook > Database Access & ORM > Entities > Accessors & Mutators
• Cookbook > Database Access & ORM > Behaviors > Translate

Answered By - ndm