Symfony API 平台在从持久性层检索实体后对其进行过滤

Question

我遇到一种情况，我需要在应用一些过滤器后从持久层中提取对象，然后对对象数据和另一个查询参数的过滤器基础进行一些数学运算。

用例：获取给定纬度和经度 10km 半径内的所有位置。

可以转换为 api 端点，如下所示：

https://api.testdomain.com/api/location?latitude=10&longitude=20&distance=10

我有位置实体：

* @ApiFilter(SearchFilter::class, properties={
*      "longitude": "start",
*      "latitude":"start",
*      "city":"partial",
*      "postal_code":"partial",
*      "address":"partial",
*    }
* )
class Location
{
... 
public function withinDistance($latitude, $longitude, $distance):?bool
{
$location_distance=$this->distanceFrom($latitude,$longitude);
return $location_distance<=$distance;
}
}

由于latitude和longitude是实体属性，因此将应用搜索并应用 sql 查询过滤器，而distance不是属性，我们必须在从数据库检索所有对象后应用这种过滤器，这对我来说很神秘。

我希望在返回查询结果后将以下代码放在某个地方：

public function getCollection($collection){
//return after search filtered applied on location.longitute and location.latitude
$all_locations_of_lat_long=$collection;
$locations_within_distance=[];
$query = $this->requestStack->getCurrentRequest()->query;
$lat= $query->get('latitude',0);
$lng= $query->get('longitude',0);
$distance= $query->get('distance',null);
if($distance==null){
return $all_locations_of_lat_long;
}
for($all_locations_of_lat_long as $location){
if($location->withinDistance($lat,$lng,$distance))
$locations_within_distance[]=$location;
}
return $locations_within_distance; 
}

为什么对返回的实体对象集合应用这样的过滤器是正确的？我不认为 在这种情况下，ORM过滤器会有所帮助。

Answer 1

我发现通过编写自定义控制器操作并在从持久性层检索实体后过滤实体来轻松过滤实体。这可能意味着我必须获取所有记录，然后过滤，这非常昂贵。

另一种选择是，正如qdequippe所建议的那样，只需编写一个自定义过滤器来查找距离，如下所示：

定义距离过滤器：

src/过滤器/距离过滤器

<?php
namespace AppFilter;

use ApiPlatformCoreBridgeDoctrineOrmFilterAbstractContextAwareFilter;
use ApiPlatformCoreBridgeDoctrineOrmUtilQueryNameGeneratorInterface;
use DoctrineORMQueryBuilder;
final class DistanceFilter extends AbstractContextAwareFilter
{
const DISTANCE=10.0;
const LAT='latitude';
const LON='longitude';
private $appliedAlready=false;
protected function filterProperty(string $property, $value, QueryBuilder $queryBuilder, QueryNameGeneratorInterface $queryNameGenerator, string $resourceClass, string $operationName = null)
{
// otherwise filter is applied to order and page as well
if ($this->appliedAlready && !$this->isPropertyEnabled($property, $resourceClass) ) {
return;
}
//make sure latitude and longitude are part of specs    
if(!($this->isPropertyMapped(self::LAT, $resourceClass) && $this->isPropertyMapped(self::LON, $resourceClass)) ){
return ;
}
$query=$this->requestStack->getCurrentRequest()->query;
$values=[];
foreach($this->properties as $prop=>$val){
$this->properties[$prop]=$query->get($prop,null);
}
//distance is optional 
if($this->properties[self::LAT]!=null && $this->properties[self::LON]!=null){
if($this->properties['distance']==null)
$this->properties['distance']=self::DISTANCE;
}else{
//may be we should raise exception 
return;
}
$this->appliedAlready=True;
// Generate a unique parameter name to avoid collisions with other filters
$latParam = $queryNameGenerator->generateParameterName(self::LAT);
$lonParam = $queryNameGenerator->generateParameterName(self::LON);
$distParam = $queryNameGenerator->generateParameterName('distance');

$locationWithinXKmDistance="(
6371.0 * acos (
cos ( radians(:$latParam) )
* cos( radians(o.latitude) )
* cos( radians(o.longitude) - radians(:$lonParam) )
+ sin ( radians(:$latParam) )
* sin( radians(o.latitude) )
)
)<=:$distParam";
$queryBuilder
->andWhere($locationWithinXKmDistance)
->setParameter($latParam, $this->properties[self::LAT])
->setParameter($lonParam, $this->properties[self::LON])
->setParameter($distParam, $this->properties['distance']);
}
// This function is only used to hook in documentation generators (supported by Swagger and Hydra)
public function getDescription(string $resourceClass): array
{
if (!$this->properties) {
return [];
}
$description = [];
foreach ($this->properties as $property => $strategy) {
$description["distance_$property"] = [
'property' => $property,
'type' => 'string',
'required' => false,
'swagger' => [
'description' => 'Find locations within given radius',
'name' => 'distance_filter',
'type' => 'filter',
],
];
}
return $description;
}
}

这个想法是，我们期望在查询字符串中latitude、longitude和可选的distance参数。如果在其中一个必需的参数上缺少过滤器，则不调用过滤器。如果缺少距离，我们将假定默认距离10km。

由于我们必须为acos、cos、sin和radians添加DQL函数，因此我们使用教义扩展如下：

安装原则扩展：

composer require beberlei/doctrineextensions

src/config/packages/doctrine_extensions.yaml

doctrine:
orm:
dql:
numeric_functions:
acos: DoctrineExtensionsQueryMysqlAcos
cos: DoctrineExtensionsQueryMysqlCos
sin: DoctrineExtensionsQueryMysqlSin
radians: DoctrineExtensionsQueryMysqlRadians

src/config/services.yaml

services:
....
AppFilterDistanceFilter:
arguments: [ '@doctrine', '@request_stack', '@?logger', {latitude: ~, longitude: ~, distance: ~} ]
tags:
- { name: 'api_platform.filter', id: 'location.distance_filter' }
autowire: false
autoconfigure: false
app.location.search_filter:
parent:        'api_platform.doctrine.orm.search_filter'
arguments:     [ {"city":"partial","postal_code":"partial","address":"partial"}]
tags:          [ { name: 'api_platform.filter', id: 'location.search_filter' } ]
autowire:  false
autoconfigure: false

在位置实体上配置 api 过滤器：

namespace AppEntity;
use AppDtoLocationOutput;
use DoctrineORMMapping as ORM;
use ApiPlatformCoreAnnotationApiResource;
use ApiPlatformCoreAnnotationApiFilter;
/**
* Location
* 
* @ApiResource(
*      collectionOperations={
*          "get"={
*              "path"="/getLocationList", 
*               "filters"={
*                      "location.distance_filter",
*                       "location.search_filter"
*                }
*           }
*      },
*      itemOperations={"get"},
*      output=LocationOutput::class
* )

Answer 2

您可以在 SQL 中应用该条件，例如在实体存储库中。

class YourEntityRepository {
public function findByLongLatDist(float lat, float long, float dist) {
// create your query builder here and return results
}
}

还要检查 https://gis.stackexchange.com/questions/31628/find-points-within-a-distance-using-mysql 以使用 MySQL 查询检索点。这个存储库 https://github.com/beberlei/DoctrineExtensions 使用特定的MySQL函数。