symfony 在单个查询中对多个字段进行筛选查询

w8biq8rn  于 2022-11-16  发布在  其他
关注(0)|答案(4)|浏览(118)

我的安装程序是Symfony 5,运行在PHP7.3上的最新API平台版本。所以我希望能够查询名称和用户名(甚至是电子邮件)。我需要写一个自定义解析器吗?
到目前为止,我已经尝试过了,但结果是WHERE name = $name AND username = $name。

query SearchUsers ($name: String!) {
  users(name: $name, username: $name) {
    edges {
       cursor
       node {
         id
         username
         email
         avatar
       }
     }
  }
}

我的实体:

/**
 * @ApiResource
 * @ApiFilter(SearchFilter::class, properties={
 *   "name": "ipartial",
 *   "username": "ipartial",
 *   "email": "ipartial",
 * })
 *
 * @ORM\Table(name="users")
 * @ORM\Entity(repositoryClass="Domain\Repository\UserRepository")
 * @ORM\HasLifecycleCallbacks()
 */
class User
{
  private $name;
  private $username;
  private $email;
  // ... code omitted ...
}
wh6knrhe

wh6knrhe1#

我为我的tutorial的第6章做了这样一个自定义过滤器。我在下面包括了它的代码。
您可以在ApiFilter标记中配置它搜索的属性。在您的情况下,这将是:

* @ApiFilter(SimpleSearchFilter::class, properties={"name", "username", "email"})

它将搜索字符串拆分为单词,并对每个单词的每个属性进行不区分大小写的搜索,因此查询字符串如下所示:

?simplesearch=Katch sQuash

将在所有指定的属性中搜索LOWER(..)LIKE '%katch%'或LOWER(..)LIKE '%squash%'
限制条件:它可能仅限于字符串属性(取决于DB),并且不按相关性排序。
代码:

// api/src/Filter/SimpleSearchFilter.php 
namespace App\Filter;

use ApiPlatform\Core\Bridge\Doctrine\Orm\Filter\AbstractContextAwareFilter;
use ApiPlatform\Core\Bridge\Doctrine\Orm\Util\QueryNameGeneratorInterface;
use Doctrine\ORM\QueryBuilder;
use Doctrine\Persistence\ManagerRegistry;
use Psr\Log\LoggerInterface;
use Symfony\Component\HttpFoundation\RequestStack;
use Symfony\Component\Serializer\NameConverter\NameConverterInterface;
use ApiPlatform\Core\Exception\InvalidArgumentException;

/**
 * Selects entities where each search term is found somewhere
 * in at least one of the specified properties.
 * Search terms must be separated by spaces.
 * Search is case insensitive.
 * All specified properties type must be string.
 * @package App\Filter
 */
class SimpleSearchFilter extends AbstractContextAwareFilter
{
    private $searchParameterName;

    /**
     * Add configuration parameter
     * {@inheritdoc}
     * @param string $searchParameterName The parameter whose value this filter searches for
     */
    public function __construct(ManagerRegistry $managerRegistry, ?RequestStack $requestStack = null, LoggerInterface $logger = null, array $properties = null, NameConverterInterface $nameConverter = null, string $searchParameterName = 'simplesearch')
    {
        parent::__construct($managerRegistry, $requestStack, $logger, $properties, $nameConverter);

        $this->searchParameterName = $searchParameterName;
    }

    /** {@inheritdoc} */
    protected function filterProperty(string $property, $value, QueryBuilder $queryBuilder, QueryNameGeneratorInterface $queryNameGenerator, string $resourceClass, string $operationName = null, array $context = [])
    {
        if (null === $value || $property !== $this->searchParameterName) {
            return;
        }

        $words = explode(' ', $value);
        foreach ($words as $word) {
            if (empty($word)) continue;

            $this->addWhere($queryBuilder, $word, $queryNameGenerator->generateParameterName($property));
        }
    }

    private function addWhere($queryBuilder, $word, $parameterName)
    {
        $alias = $queryBuilder->getRootAliases()[0];

        // Build OR expression
        $orExp = $queryBuilder->expr()->orX();
        foreach ($this->getProperties() as $prop => $ignoored) {
            $orExp->add($queryBuilder->expr()->like('LOWER('. $alias. '.' . $prop. ')', ':' . $parameterName));
        }

        $queryBuilder
            ->andWhere('(' . $orExp . ')')
            ->setParameter($parameterName, '%' . strtolower($word). '%');
    }

    /** {@inheritdoc} */
    public function getDescription(string $resourceClass): array
    {
        $props = $this->getProperties();
        if (null===$props) {
            throw new InvalidArgumentException('Properties must be specified');
        }
        return [
            $this->searchParameterName => [
                'property' => implode(', ', array_keys($props)),
                'type' => 'string',
                'required' => false,
                'swagger' => [
                    'description' => 'Selects entities where each search term is found somewhere in at least one of the specified properties',
                ]
            ]
        ];
    }

}

服务需要在api/config/services.yaml中进行配置

'App\Filter\SimpleSearchFilter':
    arguments:
        $searchParameterName: 'ignoored'

($searchParameterName实际上可以从@ApiFilter注解中配置)

w1jd8yoj

w1jd8yoj2#

API平台中默认不处理搜索过滤器中的OR条件,您需要自定义过滤器来执行此操作(https://api-platform.com/docs/core/filters/#creating-custom-filters)。
另请参阅:https://github.com/api-platform/core/issues/2400

yeotifhr

yeotifhr3#

也许你想让客户选择如何合并过滤条件和逻辑。这可以通过在“and”或“or”中嵌套过滤条件来实现,比如:

/users/?or[username]=super&or[name]=john

这将返回用户名中包含“super”或名称中包含“john”的所有用户。或者,如果您需要更复杂的逻辑和同一属性的多个条件:

/users/?and[name]=john&and[or][][email]=microsoft.com&and[or][][email]=apple.com

这将返回名称中包含john且电子邮件地址中包含microsoft. com或apple.com得所有用户.由于嵌套或,说明得条件通过AND与name得条件组合在一起,因此name得条件必须始终为true,而电子邮件得条件中只需有一个为true即可返回用户.
要在您的应用中使用此功能,请在api src/Filter文件夹(如果您还没有此文件夹,请创建此文件夹)中创建一个包含以下内容的FilterLogic.php文件:

<?php

namespace App\Filter;

use ApiPlatform\Core\Api\FilterCollection;
use ApiPlatform\Core\Bridge\Doctrine\Orm\Filter\AbstractContextAwareFilter;
use ApiPlatform\Core\Bridge\Doctrine\Orm\Filter\FilterInterface;
use ApiPlatform\Core\Bridge\Doctrine\Orm\Filter\OrderFilter;
use ApiPlatform\Core\Bridge\Doctrine\Orm\Util\QueryNameGeneratorInterface;
use ApiPlatform\Core\Metadata\Resource\Factory\ResourceMetadataFactoryInterface;
use ApiPlatform\Core\Exception\ResourceClassNotFoundException;
use Doctrine\ORM\QueryBuilder;
use Doctrine\ORM\Query\Expr;
use Doctrine\Persistence\ManagerRegistry;
use Psr\Container\ContainerInterface;
use Psr\Log\LoggerInterface;
use Symfony\Component\HttpFoundation\RequestStack;
use Symfony\Component\Serializer\NameConverter\NameConverterInterface;

/**
 * Combines existing API Platform ORM Filters with AND and OR.
 * For usage and limitations see https://gist.github.com/metaclass-nl/790a5c8e9064f031db7d3379cc47c794
 * Copyright (c) MetaClass, Groningen, 2021. MIT License
 */
class FilterLogic extends AbstractContextAwareFilter
{
    /** @var ResourceMetadataFactoryInterface  */
    private $resourceMetadataFactory;
    /** @var ContainerInterface|FilterCollection  */
    private $filterLocator;
    /** @var string Filter classes must match this to be applied with logic */
    private $classExp;

    /**
     * @param ResourceMetadataFactoryInterface $resourceMetadataFactory
     * @param ContainerInterface|FilterCollection $filterLocator
     * @param $regExp string Filter classes must match this to be applied with logic
     * {@inheritdoc}
     */
    public function __construct(ResourceMetadataFactoryInterface $resourceMetadataFactory, $filterLocator, string $classExp='//', ManagerRegistry $managerRegistry, RequestStack $requestStack=null, LoggerInterface $logger = null, array $properties = null, NameConverterInterface $nameConverter = null)
    {
        parent::__construct($managerRegistry, $requestStack, $logger, $properties, $nameConverter);
        $this->resourceMetadataFactory = $resourceMetadataFactory;
        $this->filterLocator = $filterLocator;
        $this->classExp = $classExp;
    }

    /** {@inheritdoc } */
    public function getDescription(string $resourceClass): array
    {
        // No description
        return [];
    }

    /**
     * {@inheritdoc}
     * @throws ResourceClassNotFoundException
     * @throws \LogicException if assumption proves wrong
     */
    protected function filterProperty(string $parameter, $value, QueryBuilder $queryBuilder, QueryNameGeneratorInterface $queryNameGenerator, string $resourceClass, string $operationName = null, array $context = [])
    {
        $filters = $this->getFilters($resourceClass, $operationName);

        if ($parameter == 'and') {
            $newWhere = $this->applyLogic($filters, 'and', $queryBuilder, $queryNameGenerator, $resourceClass, $operationName, $context);
            $queryBuilder->andWhere($newWhere);
        }
        if ($parameter == 'or') {
            $newWhere = $this->applyLogic($filters, 'or', $queryBuilder, $queryNameGenerator, $resourceClass, $operationName, $context);
            $queryBuilder->orWhere($newWhere);
        }
    }

    /**
     * Applies filters in compound logic context
     * @param FilterInterface[] $filters to apply in context of $operator
     * @param string $operator 'and' or 'or
     * @return mixed Valid argument for Expr\Andx::add and Expr\Orx::add
     * @throws \LogicException if assumption proves wrong
     */
    private function applyLogic($filters, $operator, $queryBuilder, $queryNameGenerator, $resourceClass, $operationName, $context)
    {
        $oldWhere = $queryBuilder->getDQLPart('where');

        // replace by marker expression
        $marker = new Expr\Func('NOT', []);
        $queryBuilder->add('where', $marker);

        $subFilters = $context['filters'][$operator];
        // print json_encode($subFilters, JSON_PRETTY_PRINT);
        $assoc = [];
        $logic = [];
        foreach ($subFilters as $key => $value) {
            if (ctype_digit((string) $key)) {
                // allows the same filter to be applied several times, usually with different arguments
                $subcontext = $context; //copies
                $subcontext['filters'] = $value;
                $this->applyFilters($filters, $queryBuilder, $queryNameGenerator, $resourceClass, $operationName, $subcontext);

                // apply logic seperately
                if (isset($value['and'])) {
                    $logic[]['and'] =  $value['and'];
                }if (isset($value['or'])) {
                    $logic[]['or'] =  $value['or'];
                }
            } elseif (in_array($key, ['and', 'or'])) {
                $logic[][$key] = $value;
            } else {
                $assoc[$key] = $value;
            }
        }

        // Process $assoc
        $subcontext = $context; //copies
        $subcontext['filters'] = $assoc;
        $this->applyFilters($filters, $queryBuilder, $queryNameGenerator, $resourceClass, $operationName, $subcontext);

        $newWhere = $queryBuilder->getDQLPart('where');
        $queryBuilder->add('where', $oldWhere); //restores old where

        // force $operator logic upon $newWhere
        if ($operator == 'and') {
            $adaptedPart = $this->adaptWhere(Expr\Andx::class, $newWhere, $marker);
        } else {
            $adaptedPart = $this->adaptWhere(Expr\Orx::class, $newWhere, $marker);
        }

        // Process logic
        foreach ($logic as $eachLogic) {
            $subcontext = $context; //copies
            $subcontext['filters'] = $eachLogic;
            $newWhere = $this->applyLogic($filters, key($eachLogic), $queryBuilder, $queryNameGenerator, $resourceClass, $operationName, $subcontext);
            $adaptedPart->add($newWhere); // empty expressions are ignored by ::add
        }

        return $adaptedPart; // may be empty
    }

    private function applyFilters($filters, $queryBuilder, $queryNameGenerator, $resourceClass, $operationName, $context)
    {
        foreach ($filters as $filter) {
            $filter->apply($queryBuilder, $queryNameGenerator, $resourceClass, $operationName, $context);
        }
    }

    /**
     * ASSUMPTION: filters do not use QueryBuilder::where or QueryBuilder::add
     * and create semantically complete expressions in the sense that expressions
     * added to the QueryBundle through ::andWhere or ::orWhere do not depend
     * on one another so that the intended logic is not compromised if they are
     * recombined with the others by either Doctrine\ORM\Query\Expr\Andx
     * or Doctrine\ORM\Query\Expr\Orx.
     *
     * Replace $where by an instance of $expClass.
     * andWhere and orWhere allways add their args at the end of existing or
     * new logical expressions, so we started with a marker expression
     * to become the deepest first part. The marker should not be returned
     * @param string $expClass
     * @param Expr\Andx | Expr\Orx $where Result from applying filters
     * @param Expr\Func $marker Marks the end of logic resulting from applying filters
     * @return Expr\Andx | Expr\Orx Instance of $expClass
     * @throws \LogicException if assumption proves wrong
     */
    private function adaptWhere($expClass, $where, $marker)
    {
        if ($where === $marker) {
            // Filters did nothing
             return new $expClass([]);
        }
 
        if (!$where instanceof Expr\Andx && !$where instanceof Expr\Orx) {
            // A filter used QueryBuilder::where or QueryBuilder::add or otherwise
            throw new \LogicException("Assumpion failure, unexpected Expression: ". $where);
        }
        $parts = $where->getParts();
        if (empty($parts)) {
            // A filter used QueryBuilder::where or QueryBuilder::add or otherwise
            throw new \LogicException("Assumpion failure, marker not found");
        }

        if ($parts[0] === $marker) {
            // Marker found, recursion ends here
            array_shift($parts);
        } else {
            $parts[0] = $this->adaptWhere($expClass, $parts[0], $marker);
        }
        return new $expClass($parts);
    }

    /**
     * @param string $resourceClass
     * @param string $operationName
     * @return FilterInterface[] From resource except $this and OrderFilters
     * @throws ResourceClassNotFoundException
     */
    protected function getFilters($resourceClass, $operationName)
    {
        $resourceMetadata = $this->resourceMetadataFactory->create($resourceClass);
        $resourceFilters = $resourceMetadata->getCollectionOperationAttribute($operationName, 'filters', [], true);

        $result = [];
        foreach ($resourceFilters as $filterId) {
            $filter = $this->filterLocator->has($filterId)
                ? $this->filterLocator->get($filterId)
                :  null;
            if ($filter instanceof FilterInterface
                && !($filter instanceof OrderFilter)
                && $filter !== $this
                && preg_match($this->classExp, get_class($filter))
            ) {
                $result[$filterId] = $filter;
            }
        }
        return $result;
    }
}

然后将以下服务配置添加到api config/services.yml中:

'App\Filter\FilterLogic':
    class: 'App\Filter\FilterLogic'
    arguments:
        - '@api_platform.metadata.resource.metadata_factory'
        - '@api_platform.filter_locator'
    public: false
    abstract: true
    autoconfigure: false

最后像这样调整实体:

use App\Filter\FilterLogic;
/**
 * @ApiResource
 * @ApiFilter(SearchFilter::class, properties={
 *   "name": "ipartial",
 *   "username": "ipartial",
 *   "email": "ipartial",
 * })
 * @ApiFilter(FilterLogic.class)
 *

您也可以通过添加@ApiFilter注解将其应用到其他类中。
可以通过配置classExp按类名加入/排除过滤器。例如:

* @ApiFilter(FilterLogic::class, arguments={"classExp"="/ApiPlatform\\Core\\Bridge\\Doctrine\\Orm\\Filter\\+/"})

将仅在逻辑上下文中应用API平台ORM筛选器

**FilterLogic类的注解是最后一个@ApiFilter注解,这一点很重要。**常规筛选仍将照常工作:过滤器决定如何将其自身应用于QueryBuilder。如果所有用途::andWhere,就像Api Platform的内置过滤器一样,ApiFilter属性/注解的顺序并不重要,但如果某些过滤器使用其他方法,则不同的顺序可能会产生不同的结果。FilterLogic使用orWhere表示“or”,因此顺序很重要。如果它是最后一个过滤器,则其逻辑表达式将成为最上面的表达式,因此定义了主要逻辑。

限制

与Api平台的内置过滤器一起使用,但具有EXCLUDE_NULL的DateFilter除外。This DateFilter子类可能会修复此问题。
假设筛选器创建语义上完整的表达式,即通过::andWhere或::orWhere添加到QueryBundle的表达式彼此不依赖,因此如果通过Doctrine\ORM\Query\Expr\Andx或Doctrine\ORM\Query\Expr\Orx将它们与其他表达式重新组合,则不会危及预期的逻辑。
如果筛选器使用QueryBuilder::where或::add,则可能失败。
建议您检查所有自定义筛选器和第三方筛选器的代码,不要合并使用QueryBuilder::where或::add的筛选器与FilterLogic组合使用,也不要将生成语义不完整的复杂逻辑的筛选器与FilterLogic组合使用。有关语义完整和不完整表达式的示例,请参阅DateFilterTest
Api Platform IMHO的内置过滤器包含一个与它们生成的JOIN相关的错误。因此,将它们与OR组合并不能像预期的那样对嵌套在to-many和可空关联上的属性起作用。我的FilterBundle提供了解决方法,但它们确实更改了ExistsFilter =false的行为。

vhipe2zx

vhipe2zx4#

感谢MetaClass和HasBert,它工作得很完美。添加嵌套属性改进的代码

<?php

namespace App\Filter;

use ApiPlatform\Core\Bridge\Doctrine\Orm\Filter\AbstractContextAwareFilter;
use ApiPlatform\Core\Bridge\Doctrine\Orm\Util\QueryNameGeneratorInterface;
use Doctrine\ORM\Query\Expr\Join;
use Doctrine\ORM\QueryBuilder;
use Doctrine\Persistence\ManagerRegistry;
use Psr\Log\LoggerInterface;
use Symfony\Component\HttpFoundation\RequestStack;
use Symfony\Component\Serializer\NameConverter\NameConverterInterface;
use ApiPlatform\Core\Exception\InvalidArgumentException;
use ApiPlatform\Core\Bridge\Doctrine\Common\PropertyHelperTrait;

class SimpleSearchFilter extends AbstractContextAwareFilter
{
    private $searchParameterName;

    /**
     * Add configuration parameter
     * {@inheritdoc}
     * @param string $searchParameterName The parameter whose value this filter searches for
     */
    public function __construct(ManagerRegistry $managerRegistry, ?RequestStack $requestStack = null, LoggerInterface $logger = null, array $properties = null, NameConverterInterface $nameConverter = null, string $searchParameterName = 'search')
    {
        parent::__construct($managerRegistry, $requestStack, $logger, $properties, $nameConverter);

        $this->searchParameterName = $searchParameterName;
    }

    /** {@inheritdoc} */
    protected function filterProperty(string $property, $value, QueryBuilder $queryBuilder, QueryNameGeneratorInterface $queryNameGenerator, string $resourceClass, string $operationName = null, array $context = [])
    {
        if (null === $value || $property !== $this->searchParameterName) {
            return;
        }

        $words = explode(' ', $value);
        foreach ($words as $word) {
            if (empty($word)) continue;
            $this->addWhere($queryBuilder, $word, $property, $queryNameGenerator,$resourceClass);
        }
    }

    private function addWhere($queryBuilder, $word, $property, $queryNameGenerator,$resourceClass)
    {
        $parameterName = $queryNameGenerator->generateParameterName($property);

        // Build OR expression
        $orExp = $queryBuilder->expr()->orX();
        foreach ($this->getProperties() as $property) {
            if ($this->isPropertyNested($property, $resourceClass)) {
                $alias = $queryBuilder->getRootAliases()[0];
                [$alias, $property ] = $this->addJoinsForNestedProperty($property, $alias, $queryBuilder, $queryNameGenerator, $resourceClass);
            }
            $orExp->add($queryBuilder->expr()->like('LOWER('. $alias. '.' . $property. ')', ':' . $parameterName));
        }

        $queryBuilder
            ->andWhere('(' . $orExp . ')')
            ->setParameter($parameterName, '%' . strtolower($word). '%');
    }
}

相关问题