CODEWITHSUNDEEP
phpmysqlcakephpcakephp-3.0spatial
robmcvey
robmcvey

Reputation: 815

CakePHP 3.5 Always apply asText() MySQL function to Spatial field

I have a custom PolygonType which represents a POLYGON() field in a MySQL table.

class PolygonType extends BaseType implements ExpressionTypeInterface
{
    public function toPHP($value, Driver $d)
    {
        // $value is binary, requires unpack()
    }
}

I can use $query->func()->astext() on every find, but I would like to know if it's possible to always apply MySQL's AsText() function when selecting this field instead (similar to how toExpression() can be used when inserting data).

Upvotes: 1

Views: 274

Answers (2)

ndm
ndm

Reputation: 60463

AFAIK there is no such functionality, type classes and select clause contents never touch.

If you wanted to apply this to all finds, then you could for example use the Model.beforeFind() event, traverse the select clause and transform the fields to expressions. Here's a quick and dirty example, where field is the name of the POLYGON type column:

// in the respective table class

use Cake\Event\Event;
use Cake\ORM\Query;

// ...

public function beforeFind(Event $event, Query $query, \ArrayObject $options, $primary)
{
    $query->traverse(
        function (&$value) use ($query) {
            if (empty($value)) {
                $value = $query->aliasFields($this->getSchema()->columns());
            }

            foreach ($value as $key => $field) {
                if (is_string($field) &&
                    $this->aliasField($field) === $this->aliasField('field')
                ) {
                    unset($value[$key]);
                    $value[key($query->aliasField($field))] = $query->func()->AsText([
                        $this->aliasField('field') => 'identifier'
                    ]);
                }
            }
        },
        ['select']
    );
}

You may have to account for $field as expressions too, in case the field might be used in one and needs to be converted there too.

Another way would be to convert the data on PHP level in the type class' toPHP() method, as already indicated in your code example.

See also

Upvotes: 2

robmcvey
robmcvey

Reputation: 815

Based on ndp's answer, it's possible to inspect the field types via $query->getDefaultTypes() and apply a SQL function as required. However, $value is empty if no fields are initially stated (e.g. when using Table::get() so there's also a check for this.

public function beforeFind(Event $event, Query $query, ArrayObject $options, $primary)
{
    $query->traverse(
        function (&$value) use ($query) {

            if (is_array($value) && empty($value)) {
                $query->all();
            }

            $defaultTypes = $query->getDefaultTypes();

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

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

Upvotes: 1

Related Questions