CakePHP 3 -在table对象中默认使用select()中的SQL函数

zy1mlcev  于 2023-05-07  发布在  PHP
关注(0)|答案(2)|浏览(177)

我正在为一个包含一些POINT列的数据库设置一个系统。我使用cookbook示例构建了一个自定义类型,它似乎可以工作。然而,要处理POINT s,我需要以特殊的方式SELECT它们:

SELECT ST_AsText(location) as location ...

这对于查询构建器来说并不难:

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

但是,我更希望这种情况在默认情况下发生。
我想使用beforeFind事件,但我找不到以下伪代码的正确函数:

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

**当一个字段将被包含时,如何将其替换为函数?**理想情况下,即使我还没有从控制器调用->select(...)

trnvg8h3

trnvg8h31#

CakePHP 3.5 Always apply asText() MySQL function to Spatial field中找到了我的解决方案。
这对于CakePHP 3.8来说很有效。我决定将事件处理放在行为中,以便于重用:

<?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']);
    }

}

在我的表对象中:

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

cs7cruho2#

我使用cake 4和一些遗留代码,因为cake 2,我使用的列的$query->getDefaultTypes();是String类型,所以解决方案不起作用...
我找到的解决方案是这样的:

public function beforeFind(\Cake\Event\EventInterface $event, \Cake\ORM\Query $query, \ArrayObject $options, $primary): void
    {
        // ->sql() transforms the query, setting the select fields when it is not setted
        $query->sql();

        $select = $query->clause('select');
        $fields = array_keys($select);

        foreach ($fields as $field) {
            if (in_array($field, [geometry fields])) {
                $select[$field] = 'ST_AsText(DeliveryFees.polygon)';
            }
        }
        $query->select($select, overwrite: true);
    }

相关问题