PHP Versions Question posted in
You can find a list of all versions here.

filter query on multiple fields in single query – Php versions

My setup is Symfony 5 with the latest API-Platform version running on PHP 7.3.
So I would like to be able to query both on name and username (maybe even email).
Do I need to write a custom resolver?

This is what I’ve tried so far but this results in a WHERE name = $name AND username = $name.

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

My entity:

/**
 * @ApiResource
 * @ApiFilter(SearchFilter::class, properties={
 *   "name": "ipartial",
 *   "username": "ipartial",
 *   "email": "ipartial",
 * })
 *
 * @ORMTable(name="users")
 * @ORMEntity(repositoryClass="DomainRepositoryUserRepository")
 * @ORMHasLifecycleCallbacks()
 */
class User
{
  private $name;
  private $username;
  private $email;
  // ... code omitted ...
}
Tags:

4

Answers


  1. 0

    The OR condition in the search filter is not handled by default in API Platform, you need a custom filter to do this (https://api-platform.com/docs/core/filters/#creating-custom-filters).

    See also: https://github.com/api-platform/core/issues/2400.

    Login or Signup to reply.
  2. 0

    I made such a custom filter for chapter 6 of my tutorial. I include its code below.

    You can configure which properties it searches in the ApiFilter attribute. In your case that would be:

     #[ApiFilter(filterClass: SimpleSearchFilter::class,
properties: ['name', 'username', 'email'])]

    It splits the search string into words and searches each of the properties case insensitive for each word, so a query string like:

    ?simplesearch=Katch sQuash

    will search in all specified properties both LOWER(..) LIKE ‘%katch%’ OR LOWER(..) LIKE ‘%squash%’

    Limitations: It may be limited to string properties (depending on the DB) and it does not sort by relevance.

    The code (apip 3.0):

    <?php

namespace AppFilter;

use ApiPlatformDoctrineOrmFilterAbstractFilter;
use ApiPlatformDoctrineOrmUtilQueryNameGeneratorInterface;
use ApiPlatformMetadataOperation;
use DoctrineORMQueryExprJoin;
use DoctrineORMQueryBuilder;
use DoctrinePersistenceManagerRegistry;
use PsrLogLoggerInterface;
use SymfonyComponentSerializerNameConverterNameConverterInterface;
use ApiPlatformExceptionInvalidArgumentException;

/**
 * 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. Nested properties are supported.
 * @package AppFilter
 */
class SimpleSearchFilter extends AbstractFilter
{
    private $searchParameterName;

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

        $this->searchParameterName = $searchParameterName;
    }

    /** {@inheritdoc} */
    protected function filterProperty(string $property, $value, QueryBuilder $queryBuilder, QueryNameGeneratorInterface $queryNameGenerator, string $resourceClass, Operation $operation = null, array $context = []): void
    {
        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), $queryNameGenerator, $resourceClass);
        }
    }

    private function addWhere($queryBuilder, $word, $parameterName, $queryNameGenerator, $resourceClass)
    {
        // Build OR expression
        $orExp = $queryBuilder->expr()->orX();
        foreach ($this->getProperties() as $prop => $ignoored) {
            $alias = $queryBuilder->getRootAliases()[0];
            // Thanks to Hasbert and Polo
            if ($this->isPropertyNested($prop, $resourceClass)) {
                [$alias, $prop] = $this->addJoinsForNestedProperty($prop, $alias, $queryBuilder, $queryNameGenerator, $resourceClass, Join::INNER_JOIN);
            }

            $orExp->add($queryBuilder->expr()->like('LOWER('. $alias. '.' . $prop. ')', ':' . $parameterName));
        }

        // Add it
        $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',
                ]
            ]
        ];
    }

}

    The service needs configuration in api/config/services.yaml

    'AppFilterSimpleSearchFilter':
    arguments:
        $searchParameterName: 'ignoored'

    ($searchParameterName can actually be configured from the #ApiFilter attribute)

    Login or Signup to reply.
  3. 0

    Maybe you want let the clients choose how to combine filter criteria and logic. This can be done by nesting the filter criteria in "and" or "or, like:

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

    This will return all users with "super" in their username OR "john" in their name. Or if you need more complex logic and multiple criteria for the same property:

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

    This will return all users with john in their names AND (microsoft.com or apple.com in their email address). Because of the nesting of or the criteria for the description are combined together through AND with the criterium for name, which must allways be true while only one of the criteria for the email needs to be true for a user to be returned.

    To make this work within your app create a file FilterLogic.php in your api src/Filter folder
    (create this folder if you don’t have one yet) with the following content:

        <?php

namespace AppFilter;

use ApiPlatformDoctrineOrmFilterFilterInterface;
use ApiPlatformDoctrineOrmFilterOrderFilter;
use ApiPlatformDoctrineOrmUtilQueryNameGeneratorInterface;
use ApiPlatformMetadataOperation;
use DoctrineORMQueryBuilder;
use DoctrineORMQueryExpr;
use DoctrinePersistenceManagerRegistry;
use PsrContainerContainerInterface;
use PsrLogLoggerInterface;
use SymfonyComponentSerializerNameConverterNameConverterInterface;
use DoctrineORMQueryExprJoin;

/**
 * Combines existing API Platform ORM Filters with AND and OR.
 * For usage and limitations see https://github.com/metaclass-nl/filter-bundle/blob/master/README.md
 *
 * Copyright (c) MetaClass, Groningen, 2021-2022. MIT License
 */
class FilterLogic implements FilterInterface
{
    /** @var ContainerInterface  */
    private $filterLocator;
    /** @var string Filter classes must match this to be applied with logic */
    private $classExp;
    /** @var FilterInterface[] */
    private $filters;

    /**
     * @param ContainerInterface $filterLocator
     * @param $regExp string Filter classes must match this to be applied with logic
     * @param $innerJoinsLeft bool Wheather to replace all inner joins by left joins.
     *   This makes the standard Api Platform filters combine properly with OR,
     *   but also changes the behavior of ExistsFilter =false.
     * {@inheritdoc}
     */
    public function __construct(ContainerInterface $filterLocator, ManagerRegistry $managerRegistry, ?LoggerInterface $logger = null, ?array $properties = null, ?NameConverterInterface $nameConverter = null, string $classExp='//')
    {
        $this->filterLocator = $filterLocator;
        $this->classExp = $classExp;
    }

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

    /**
     * {@inheritdoc}
     * @throws LogicException if assumption proves wrong
     */
    public function apply(QueryBuilder $queryBuilder, QueryNameGeneratorInterface $queryNameGenerator, string $resourceClass, Operation $operation = null, array $context = []): void
    {
        if (!isset($context['filters']) || !is_array($context['filters'])) {
            throw new InvalidArgumentException('::apply without $context[filters] not supported');
        }

        $this->filters = $this->getFilters($operation);

        $logic = false; #15 when no where filter is used, do not replace inner joins by left joins
        if (isset($context['filters']['and']) ) {
            $expressions = $this->filterProperty('and', $context['filters']['and'], $queryBuilder, $queryNameGenerator, $resourceClass, $operation, $context);
            foreach($expressions as $exp) {
                $queryBuilder->andWhere($exp);
                $logic = true;
            };
        }
        if (isset($context['filters']['not']) ) {
            // NOT expressions are combined by parent logic, here defaulted to AND
            $expressions = $this->filterProperty('not', $context['filters']['not'], $queryBuilder, $queryNameGenerator, $resourceClass, $operation, $context);
            foreach($expressions as $exp) {
                $queryBuilder->andWhere(new ExprFunc('NOT', [$exp]));
                $logic = true;
            };
        }
        #Issue 10: for security allways AND with existing criteria
        if (isset($context['filters']['or'])) {
            $expressions = $this->filterProperty('or', $context['filters']['or'], $queryBuilder, $queryNameGenerator, $resourceClass, $operation, $context);
            if (!empty($expressions)) {
                $queryBuilder->andWhere(new ExprOrx($expressions));
                $logic = true;
            }
        }

    }

    /**
     * @throws LogicException if assumption proves wrong
     */
    protected function doGenerate($queryBuilder, $queryNameGenerator, $resourceClass, Operation $operation = null, $context)
    {
        if (empty($context['filters'])) {
            return [];
        }
        $oldWhere = $queryBuilder->getDQLPart('where');

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

        $assoc = [];
        $logic = [];
        foreach ($context['filters'] 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($queryBuilder, $queryNameGenerator, $resourceClass, $operation, $subcontext);

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

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

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

        // force $operator logic upon $newWhere
        $expressions = $this->getAppliedExpressions($newWhere, $marker);

        // Process logic
        foreach ($logic as $eachLogic) {
            $subExpressions = $this->filterProperty(key($eachLogic), current($eachLogic), $queryBuilder, $queryNameGenerator, $resourceClass, $operation, $context);
            if (key($eachLogic) == 'not') {
                // NOT expressions are combined by parent logic
                foreach ($subExpressions as $subExp) {
                    $expressions[] = new ExprFunc('NOT', [$subExp]);
                }
            } else {
                $expressions[] = key($eachLogic) == 'or'
                    ? new ExprOrx($subExpressions)
                    : new ExprAndx($subExpressions);
            }
        }

        return $expressions; // may be empty
    }

    /**
     * @throws LogicException if assumption proves wrong
     */
    protected function filterProperty(string $property, $value, QueryBuilder $queryBuilder, QueryNameGeneratorInterface $queryNameGenerator, string $resourceClass, Operation $operation = null, $context=[])
    {
        $subcontext = $context; //copies
        $subcontext['filters'] = $value;
        return $this->doGenerate($queryBuilder, $queryNameGenerator, $resourceClass, $operation, $subcontext);
    }

    /** Calls ::apply on each filter in $filters */
    private function applyFilters($queryBuilder, $queryNameGenerator, $resourceClass, Operation $operation = null, $context)
    {
        foreach ($this->filters as $filter) {
            $filter->apply($queryBuilder, $queryNameGenerator, $resourceClass, $operation, $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 DoctrineORMQueryExprAndx
     * or DoctrineORMQueryExprOrx.
     *
     * Get expressions from $where
     * 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 ExprAndx | ExprOrx $where Result from applying filters
     * @param ExprFunc $marker Marks the end of logic resulting from applying filters
     * @return array of ORM Expression
     * @throws LogicException if assumption proves wrong
     */
    private function getAppliedExpressions($where, $marker)
    {
        if ($where === $marker) {
            return [];
        }
        if (!$where instanceof ExprAndx && !$where instanceof ExprOrx) {
            // 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");
        }

        $firstPart = array_shift($parts);
        $parts = array_merge($parts, $this->getAppliedExpressions($firstPart, $marker));
        return $parts;
    }


    /**
     * @param Operation $operation
     * @return FilterInterface[] From resource except $this and OrderFilters
     */
    protected function getFilters(Operation $operation = null)
    {
        $resourceFilters = $operation ? $operation->getFilters() : [];

        $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;
    }
}

    Then add the following service configuration to your api config/services.yml:

    'AppFilterFilterLogic':
    class: 'AppFilterFilterLogic'
    arguments:
        - '@api_platform.filter_locator'
    public: false
    abstract: true
    autoconfigure: false

    Finally adapt your entity like this:

    use AppFilterFilterLogic;
#[ApiResource]
#[ApiFilter(SearchFilter::class, properties: ['id' => 'exact', 'price' => 'exact', 'description' => 'partial'])]
#[ApiFilter(FilterLogic::class)]

    You can apply it in other classes as well just by adding the @ApiFilter annotation.

    Limitations

    This version is for Api Platform 3.0 and 2.7 with metadata_backward_compatibility_layer set to false. For older versions see FilterBundle.

    For reasons of security Expressions that are nested in "and", "or" or "not" are allways combined with normal
    expressions by AND.

    Works with built in filters of Api Platform, except for DateFilter with EXCLUDE_NULL.
    This DateFilter subclass may fix it.

    Assumes that filters 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 DoctrineORMQueryExprAndx or DoctrineORMQueryExprOrx.

    May Fail if a filter uses QueryBuilder::where or ::add.

    You are advised to check the code of all custom and third party Filters and
    not to combine those that use QueryBuilder::where or ::add with FilterLogic
    or that produce complex logic that is not semantically complete. For an
    example of semantically complete and incomplete expressions see DateFilterTest.

    The built in filters of Api Platform IMHO contain a bug with respect to the JOINs they generate. As a result, combining them with OR does not work as expected with properties nested over to-many and nullable associations. FilterBundle provides workarounds, but they do change the behavior of ExistsFilter =false.

    Login or Signup to reply.
  4. 0

    Thanks MetaClass and HasBert, it works perfectly. The code to add the improvment of nested properties

    <?php

namespace AppFilter;

use ApiPlatformCoreBridgeDoctrineOrmFilterAbstractContextAwareFilter;
use ApiPlatformCoreBridgeDoctrineOrmUtilQueryNameGeneratorInterface;
use DoctrineORMQueryExprJoin;
use DoctrineORMQueryBuilder;
use DoctrinePersistenceManagerRegistry;
use PsrLogLoggerInterface;
use SymfonyComponentHttpFoundationRequestStack;
use SymfonyComponentSerializerNameConverterNameConverterInterface;
use ApiPlatformCoreExceptionInvalidArgumentException;
use ApiPlatformCoreBridgeDoctrineCommonPropertyHelperTrait;

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). '%');
    }
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search