php - Laravel + Doctrine - 通过 ST_DISTANCE 获得最近的地方

Question

我想找到区域点和我的点之间的最小距离。我搜索它并找到 ST_CONTAIN ，但是当我使用这个函数时，我得到一个错误:

Expected known function, got 'ST_Distance'

这是我的代码:

public function getNearestPlace(Point $point)
{
    $neighborhoods = $this->_em
        ->createQueryBuilder()
        ->select('Region')
        ->from($this->entityClass, 'Region')
        ->where(
            '
                MIN (ST_Distance(
                    Region.regionMapCenter, 
                    GeomFromText(\'POINT(' . $point . ')\')
                ) > 0
            '
        )
        ->getQuery()
        ->execute();
    return $neighborhoods;
}

如何解决？

Answer 1

Doctrine 与 some functions out-of-the-box 一起提供但距离函数不在其中。不过，您可以定义自己的函数，这可能对您的情况有用。可以找到这方面的文档 in chapter 14.5.5. Adding your own functions to the DQL language

我还找到了a nice blog post关于如何为您的应用程序添加距离功能。也许这篇博文也会有所帮助。博文中的距离函数如下所示:

<?php
namespace Wantlet\ORM;

use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\Lexer;

/**
 * DQL function for calculating distances between two points
 *
 * Example: DISTANCE(foo.point, POINT_STR(:param))
 */
class Distance extends FunctionNode {
    private $firstArg;
    private $secondArg;

    public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker) {
        //Need to do this hacky linestring length thing because
        //despite what MySQL manual claims, DISTANCE isn't actually implemented...
        return 'GLength(LineString(' .
               $this->firstArg->dispatch($sqlWalker) .
               ', ' .
               $this->secondArg->dispatch($sqlWalker) .
           '))';
    }

    public function parse(\Doctrine\ORM\Query\Parser $parser) {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->firstArg = $parser->ArithmeticPrimary();
        $parser->match(Lexer::T_COMMA);
        $this->secondArg = $parser->ArithmeticPrimary();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }
}