CakePHP 3 - Use SQL function in select() by default in table object

Question

I am setting up a system for a database with some POINT columns. I used the cookbook example to build a custom type and it seems to be working. However, to process the POINTs I need to SELECT them in a special way:

SELECT ST_AsText(location) as location ...

This isn't hard with the query builder:

$this->Houses->find()->select(['location' => 'ST_AsText(location)'])

However, I would prefer to have this happen by default.

I was thinking of using the beforeFind event, but I cannot find the right functions for the following pseudo-code:

public function beforeFind(Event $event, Query $query, ArrayObject $options, $primary)
{
    if 'location' in query->getSelectedFields():
         replace 'location' by 'location' => 'ST_AsText(location)'
}

How can I have a field be replaced by a function when it is going to be included? Ideally even when I haven't called ->select(...) yet from a controller.

Older comparable question on CakePHP discourse: https://discourse.cakephp.org/t/read-data-from-spatial-mysql-field-point-polygon/2124

Answer 1

Found my solution in CakePHP 3.5 Always apply asText() MySQL function to Spatial field.

This works well for CakePHP 3.8. I decided to put the event handling inside a behavior to make it easy to reuse:

<?php
// src/Model/Behavior/SpatialBehavior.php    

namespace App\Model\Behavior;

use Cake\ORM\Behavior;
use Cake\ORM\Table;
use Cake\Event\Event;
use ArrayObject;
use Cake\ORM\Query;

/**
 * Spatial behavior
 * 
 * Make sure spatial columns are loaded as text when needed.
 * 
 * @property \Cake\ORM\Table $_table
 */
class SpatialBehavior extends Behavior
{

    /**
     * Default configuration.
     *
     * @var array
     */
    protected $_defaultConfig = [];

    /**
     * Callback before each find is executed
     * 
     * @param Event $event
     * @param Query $query
     * @param ArrayObject $options
     * @param type $primary
     */
    public function beforeFind(Event $event, Query $query, ArrayObject $options, $primary)
    {
        $query->traverse(function (&$value) use ($query)
        {
            if (is_array($value) && empty($value))
            {
                // Built up standard query when ->select() was never used
                //$query->all(); // Execute query to learn columns
                $query->select($this->_table);
            }

            $defaultTypes = $query->getDefaultTypes();

            foreach ($value as $key => $field)
            {
                if (in_array($defaultTypes[$field], ['point', 'polygon']))
                {
                    $value[$key] = $query->func()->ST_AsText([
                        $this->_table->aliasField($field) => 'identifier'
                    ]);
                }
            }

            $query->select($value);
        }, ['select']);
    }

}

In my table object:

class HousesTable extends Table
{
    public function initialize(array $config)
    {
        //...
        $this->addBehavior('Spatial');
        //...
    }