如何在Shopware 6 DAL中为自定义Location实体实现Haversine公式？

我有一个自定义的Shopware 6实体Location，其中包含用于存储坐标的lat（纬度）和lng（经度）字段。根据官方文档要求，Store API路由必须使用Shopware DAL（数据抽象层）实现，我想在Store API路由中通过常规Shopware DAL实现Haversine公式，请问是否可行？

以下是该公式的原生SQL实现：

SELECT id, ( 6371 * acos( cos( radians(input_latitude) ) * cos( radians(latitude) ) 
* cos( radians( longitude ) - radians(100.56133310918271) ) + sin( radians(input_longitude) ) * sin(radians(input_latitude)) ) ) AS distance 
FROM location 
HAVING distance < 1 
ORDER BY distance 
LIMIT 0 , 500;

Shopware 6的DAL本身没有直接封装Haversine公式，但可以通过以下几种方式实现需求：

1. 直接扩展QueryBuilder注入SQL片段 #

Shopware DAL底层基于Doctrine ORM，你可以在DAL查询中获取QueryBuilder，直接注入Haversine公式的SQL逻辑：

use Shopware\Core\Framework\Context;
use Shopware\Core\Framework\DataAbstractionLayer\EntityRepository;
use Doctrine\DBAL\Query\QueryBuilder;

/** @var EntityRepository $locationRepository */
/** @var Context $context */

$inputLat = 你的输入纬度;
$inputLng = 你的输入经度;
$maxDistance = 1;

// 创建QueryBuilder并编写Haversine查询逻辑
$queryBuilder = $locationRepository->getContext()->getConnection()->createQueryBuilder();
$queryBuilder->select('id', '( 6371 * acos( cos( radians(:inputLat) ) * cos( radians(lat) ) * cos( radians(lng) - radians(:inputLng) ) + sin( radians(:inputLat) ) * sin( radians(:inputLng) ) ) ) AS distance')
    ->from('location')
    ->having('distance < :maxDistance')
    ->orderBy('distance')
    ->setParameter('inputLat', $inputLat)
    ->setParameter('inputLng', $inputLng)
    ->setParameter('maxDistance', $maxDistance)
    ->setMaxResults(500);

// 获取原始查询结果
$rawResults = $queryBuilder->executeQuery()->fetchAllAssociative();
// 如需映射为Location实体，可使用repository的map方法
$locationEntities = $locationRepository->map($rawResults, $context);

2. 自定义Doctrine DQL函数封装Haversine公式 #

通过注册自定义DQL函数，可将Haversine公式封装为可复用的函数，直接在DAL的Criteria中调用：

步骤1：创建自定义DQL函数类 #

// src/Doctrine/HaversineDistanceFunction.php
namespace YourPlugin\Doctrine;

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

class HaversineDistanceFunction extends FunctionNode
{
    private $inputLat;
    private $inputLng;
    private $entityLat;
    private $entityLng;

    public function parse(Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->inputLat = $parser->ArithmeticPrimary();
        $parser->match(Lexer::T_COMMA);
        $this->inputLng = $parser->ArithmeticPrimary();
        $parser->match(Lexer::T_COMMA);
        $this->entityLat = $parser->ArithmeticPrimary();
        $parser->match(Lexer::T_COMMA);
        $this->entityLng = $parser->ArithmeticPrimary();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }

    public function getSql(SqlWalker $sqlWalker)
    {
        return sprintf(
            '6371 * acos( cos( radians(%s) ) * cos( radians(%s) ) * cos( radians(%s) - radians(%s) ) + sin( radians(%s) ) * sin( radians(%s) ) )',
            $this->inputLat->dispatch($sqlWalker),
            $this->entityLat->dispatch($sqlWalker),
            $this->entityLng->dispatch($sqlWalker),
            $this->inputLng->dispatch($sqlWalker),
            $this->inputLat->dispatch($sqlWalker),
            $this->entityLng->dispatch($sqlWalker)
        );
    }
}

步骤2：注册DQL函数 #

在插件的services.xml中添加以下配置：

<service id="YourPlugin\Doctrine\HaversineDistanceFunction" public="false">
    <tag name="doctrine.dql.function" function="HAVERSINE_DISTANCE" />
</service>

步骤3：在DAL查询中使用 #

use Shopware\Core\Framework\DataAbstractionLayer\Search\Criteria;
use Shopware\Core\Framework\DataAbstractionLayer\Search\Filter\HavingFilter;
use Shopware\Core\Framework\DataAbstractionLayer\Search\Sorting\FieldSorting;
use Shopware\Core\Framework\DataAbstractionLayer\Search\Extension\AggregationExtension;
use Shopware\Core\Framework\DataAbstractionLayer\Search\Aggregation\Metric\AvgAggregation;

$inputLat = 你的输入纬度;
$inputLng = 你的输入经度;
$maxDistance = 1;

$criteria = new Criteria();
// 添加距离计算的扩展
$criteria->addExtension('distance', new AggregationExtension(
    new AvgAggregation('distance', sprintf('HAVERSINE_DISTANCE(%f, %f, lat, lng)', $inputLat, $inputLng))
));
// 添加距离过滤条件
$criteria->addFilter(new HavingFilter(HavingFilter::OPERATOR_LT, 'distance', $maxDistance));
// 按距离升序排序
$criteria->addSorting(new FieldSorting('distance', FieldSorting::ASCENDING));
// 设置返回数量限制
$criteria->setLimit(500);

// 执行查询
$locations = $locationRepository->search($criteria, $context);

3. 在自定义Repository中封装查询逻辑 #

最直接的方式是在自定义的LocationRepository中封装附近位置查询的方法，对外提供调用入口：

// src/Repository/LocationRepository.php
namespace YourPlugin\Repository;

use Shopware\Core\Framework\Context;
use Shopware\Core\Framework\DataAbstractionLayer\EntityRepository;

class LocationRepository extends EntityRepository
{
    public function searchNearby(float $lat, float $lng, float $maxDistance, int $limit = 500, Context $context): array
    {
        $connection = $this->getContext()->getConnection();
        $query = $connection->createQueryBuilder()
            ->select('*', '( 6371 * acos( cos( radians(:lat) ) * cos( radians(lat) ) * cos( radians(lng) - radians(:lng) ) + sin( radians(:lat) ) * sin( radians(:lng) ) ) ) AS distance')
            ->from('location')
            ->having('distance < :maxDistance')
            ->orderBy('distance')
            ->setParameter('lat', $lat)
            ->setParameter('lng', $lng)
            ->setParameter('maxDistance', $maxDistance)
            ->setMaxResults($limit);

        $rawResults = $query->executeQuery()->fetchAllAssociative();
        
        // 将结果映射为Location实体返回
        return $this->map($rawResults, $context);
    }
}

之后在Store API控制器中直接调用searchNearby方法即可完成查询。

内容的提问来源于stack exchange，提问作者Abenil