CakePHP 3: Table obj. defaults to SQL func. in select()

Programming
, , ,
1

I am setting up a system for a database with some POINT columns. I need to SELECT them using ST_AsText(location) to process the POINTs. I was able to achieve this using Query Builder:

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

However, I would like to have this happen by default, even when I haven’t called ->select(...) yet from a controller. To do this, I was thinking of using the beforeFind event. How can I replace a field with a function when it is going to be included?

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

2

You can use the beforeFind event in your HousesTable class to modify the query before it is executed. Here’s an example of how you can replace the location field with ST_AsText(location):

// In HousesTable.php

use Cake\Event\EventInterface;

class HousesTable extends Table
{
    // ...

    public function beforeFind(EventInterface $event, Query $query, ArrayObject $options, $primary)
    {
        $query->select(['location' => 'ST_AsText(location)']);
    }

    // ...
}

This will modify the select clause of the query to replace the location field with ST_AsText(location) by default.