How is it possible to correctly nest custom fields in a CakePHP find query?

Question

we're often dealing with a code that looks like the following:

return $this->find(
'all', [
  'fields' => ['DISTINCT Tag.tag', 'COUNT(Tag.tag) as count'],
  'group' => ['Tag.tag'],
  'order' => ['count' => 'DESC']
]);

This query leads us to the following output:

[0] => Array
    (
        [Tag] => Array
            (
                [tag] => walls.io
            )

        [0] => Array
            (
                [count] => 15
            )

    )

As you can see, the query returns the results in a "somehow wrong" nesting. The "count" field is unfortunately put into a pseudo [0]-array.

IIRC, CakePHP uses internally a syntax like Tag__field for correctly nesting virtual fields.

When changing the code to the Model__-syntax, the problem stays the same:

return $this->find(
'all', [
  'fields' => ['DISTINCT Tag.tag', 'COUNT(Tag.tag) as Tag__count'],
  'group' => ['Tag.tag'],
  'order' => ['COUNT(Tag.tag)' => 'DESC']
]);

Output:

[0] => Array
    (
        [Tag] => Array
            (
                [tag] => walls.io
            )

        [0] => Array
            (
                [Tag__count] => 15
            )

    )

Workaround 1: array_map

CakePHP pros: Is there a better/more elegant solution than manually mapping the array after the select statement?

$tags = array_map(function($tag) {
  $tag['Tag']['count'] = $tag[0]['count'];
  unset($tag[0]);

  return $tag;
}, $tags);

Workaround 2: virtual field

As described above, the usage of a virtual field might solve this problem:

$this->virtualFields = ['count' => 'COUNT(Tag.Tag)'];
return $this->find(
'all', [
  'group' => ['Tag.tag'],
  'order' => [$this->getVirtualField('count') => 'DESC']
]);

Unfortunately, with this solution, it's not possible to specify ANY fields at all. only by completely leaving the "fields"-key, the nesting of the array works as expected. when selecting $fields = ['Tag.tag', $this->getVirtualField('count')] the nesting is wrong again.

CakePHP pros: Do you know a method, where the nesting is done right, even if you specify your own fields?

Answer 1

Looking at the CakePHP code, such a method does not exist.

Have a look at the file lib/Cake/Model/Datasource/Database/Mysql.php.

Find the method called: Mysql::resultSet( $results ); (around line 240).

That method maps the result to an array. To determine if a column is part of a table or not it uses PDOStatement::getColumnMeta(). For your "virtual column" that method will return an empty table and so the CakePHP code will put it separately, see the else branch

$this->map[$index++] = array(0, $column['name'], $type);

In order to avoid that else branch you would have to use Virtual fields, but then you run into the other problems that you have noticed.

So you are left with the array_map solution or you could try to overload that Mysql class and add your custom logic at how to identify where a column fits.