Api platform OR search filter

OrSearchFilter.php

<?php

namespace Axelvkn\AppBundle\Filter;

use ApiPlatform\Core\Bridge\Doctrine\Orm\Filter\SearchFilter;
use ApiPlatform\Core\Bridge\Doctrine\Orm\Util\QueryNameGeneratorInterface;
use ApiPlatform\Core\Exception\InvalidArgumentException;
use Doctrine\ORM\QueryBuilder;

class OrSearchFilter extends SearchFilter
{
    /**
     * {@inheritDoc}
     */
    protected function addWhereByStrategy(string $strategy, QueryBuilder $queryBuilder, QueryNameGeneratorInterface $queryNameGenerator, string $alias, string $field, $value, bool $caseSensitive)
    {
        $wrapCase = $this->createWrapCase($caseSensitive);
        $valueParameter = $queryNameGenerator->generateParameterName($field);

        switch ($strategy) {
            case null:
            case self::STRATEGY_EXACT:
                $queryBuilder
                    ->orWhere(sprintf($wrapCase('%s.%s').' = '.$wrapCase(':%s'), $alias, $field, $valueParameter))
                    ->setParameter($valueParameter, $value);
                break;
            case self::STRATEGY_PARTIAL:
                $queryBuilder
                    ->orWhere(sprintf($wrapCase('%s.%s').' LIKE '.$wrapCase('CONCAT(\'%%\', :%s, \'%%\')'), $alias, $field, $valueParameter))
                    ->setParameter($valueParameter, $value);
                break;
            case self::STRATEGY_START:
                $queryBuilder
                    ->orWhere(sprintf($wrapCase('%s.%s').' LIKE '.$wrapCase('CONCAT(:%s, \'%%\')'), $alias, $field, $valueParameter))
                    ->setParameter($valueParameter, $value);
                break;
            case self::STRATEGY_END:
                $queryBuilder
                    ->orWhere(sprintf($wrapCase('%s.%s').' LIKE '.$wrapCase('CONCAT(\'%%\', :%s)'), $alias, $field, $valueParameter))
                    ->setParameter($valueParameter, $value);
                break;
            case self::STRATEGY_WORD_START:
                $queryBuilder
                    ->orWhere(sprintf($wrapCase('%1$s.%2$s').' LIKE '.$wrapCase('CONCAT(:%3$s, \'%%\')').' OR '.$wrapCase('%1$s.%2$s').' LIKE '.$wrapCase('CONCAT(\'%% \', :%3$s, \'%%\')'), $alias, $field, $valueParameter))
                    ->setParameter($valueParameter, $value);
                break;
            default:
                throw new InvalidArgumentException(sprintf('strategy %s does not exist.', $strategy));
        }
    }
}

services.yml

services:
    axelvnk.filter.or_search_filter:
        class: Axelvnk\AppBundle\Filter\OrSearchFilter
        parent: "api_platform.doctrine.orm.search_filter"
        
    axelvnk.filter.customer:
        parent: axelvnk.filter.or_search_filter
        arguments:
            - { name: "partial", vatNumber: "partial" }
        tags:
            - { name: "api_platform.filter", id: "customer.search" }

#now you can search /api/customers?name=0844.010.460&vatNumber=0844.010.460 and your filter will be applied with all or conditions!

Thanks mate, there is one small issue that when using orWhere it returns all the DB rows while using andWhere it works normally as with the regular SearcFilter

Thank you! This works perfect for multiple search cases!

Thx for the code

#now you can search /api/customers?name=0844.010.460&name=0844.010.460 and your filter will be applied with all or conditions!

The name query parameter is repeated, IIRC PHP will see only one value name.

@axelvnk Should it be something like /api/customers?name[]=0844.010.460&name[]=Bob?

@alexisLefebvre nice catch, i updated the comment

Thx !

Thx! Works nice but there is an issue with properties of nullable mapped ressources:
If a property of a nullable mapped ressource is involved the search result contains only entites where the mapped ressource is not null

Detailed explanation:

My UserClass looks like this (incomplete Example)
 * @ApiFilter(OrSearchFilter::class, properties={
 *      "username": "ipartial", "lastname": "ipartial", "updatedBy.username": "ipartial"
 * })
 * 
class User
{
    /**
     * @ORM\Column(type="string", length=180, unique=true)
     */
    private $username;

    /**
     * @ORM\Column(type="string", length=180, unique=true)
     */
    private $lastname;

    /**
     * @ORM\ManyToOne(targetEntity=User::class)
     * @ORM\JoinColumn(nullable=true)
     */
    protected $updatedBy;
}

TestUsers:

{@id: 1, username: user1, lastname: testuser1, updatedBy: null}
{@id: 2, username: user2, lastname: testuser2, updatedBy: null}
{@id: 3, username: user3, lastname: testuser2, updatedBy: /users/1}
{@id: 4, username: what, lastname: ever, updatedBy: /users/1}

Situation 1: /api/users?username=user&lastname=user
The search for username and lastname works as expected and returns all TestUsers 1-3

Situation 2: /api/users?username=user&lastname=user&updatedBy.username=user
The search for username, lastname and updatedBy.username should return all 4 TestUsers (The 3 users from situation 1 and and the 4th because user matches the updatedBy.username username of user1). But the result includes only TestUser 3-4 where is property updatedBy is not null.

Does anyone have an ideo how to solve this problem?

Thanks so much for posting this! Saved my rear in a pinch.

Also, I wanted to note that, if you're using PHP8 attributes, you don't need to add anything to services.yml. Just add the OrSearchFilter.php class somewhere in your project and then reference it in the ApiFilter attribute (above your property), like so:

/**
 * @ORM\ManyToOne(targetEntity=Profile::class, inversedBy="profileSurveys")
 * @ORM\JoinColumn(nullable=false)
 */
#[ApiFilter(OrSearchFilter::class, properties: ['profile.isMentor' => 'exact', 'profile.isRevMentor' => 'exact', 'profile.isMentee' => 'exact'])]
private $profile;

https://gist.github.com/axelvnk/edf879af5c7dbd9616a4eeb77c7181a3#file-orsearchfilter-php-L21 should be unreachable because the parameter is typed as string. string $strategy

Is it possible to use this Filter and the Searchfilter at the same time ?
We have on the left side a few filters e.g. city, but on the top we also want the OrSearchFilter to search on almsot any property of the Entity.

So if someone search for e.g. test123 on the top search we see want to see all entities which has test123 in at least one of the defined properties (orSearchFilter).
If we also set the city filter e.g. new york on the left side we want to see all Entities which are located in new york AND have test123 in at least one of the defined properties (orSearchFilter).

@UtechtDustin this code add or conditions so it should work with other filters. Did you try it?

Maybe you don't need this filter but instead create a filter to search in several properties: https://api-platform.com/docs/core/filters/#creating-custom-filters See also this answer on Stack Overflow.

Unfortunately doesn't work with API Platform 3.0

I write this if you want :

<?php

namespace App\Filter;

use ApiPlatform\Api\IdentifiersExtractorInterface;
use ApiPlatform\Api\IriConverterInterface;
use ApiPlatform\Doctrine\Common\Filter\SearchFilterInterface;
use ApiPlatform\Doctrine\Common\Filter\SearchFilterTrait;
use ApiPlatform\Doctrine\Orm\Filter\AbstractFilter;
use ApiPlatform\Doctrine\Orm\Util\QueryNameGeneratorInterface;
use ApiPlatform\Exception\InvalidArgumentException;
use ApiPlatform\Metadata\Operation;
use Closure;
use Doctrine\ORM\Query\Expr\Join;
use Doctrine\ORM\QueryBuilder;
use Doctrine\Persistence\ManagerRegistry;
use Psr\Log\LoggerInterface;
use Symfony\Component\PropertyAccess\PropertyAccess;
use Symfony\Component\PropertyAccess\PropertyAccessorInterface;
use Symfony\Component\Serializer\NameConverter\NameConverterInterface;

final class SearchMultiFieldsFilter extends AbstractFilter implements SearchFilterInterface
{
    use SearchFilterTrait;

    public function __construct(
        ManagerRegistry $managerRegistry,
        IriConverterInterface $iriConverter,
        ?PropertyAccessorInterface $propertyAccessor = null,
        ?LoggerInterface $logger = null,
        ?array $properties = null,
        ?IdentifiersExtractorInterface $identifiersExtractor = null,
        ?NameConverterInterface $nameConverter = null,
        public string $searchParameterName = 'search',
    ) {
        parent::__construct($managerRegistry, $logger, $properties, $nameConverter);

        $this->iriConverter = $iriConverter;
        $this->identifiersExtractor = $identifiersExtractor;
        $this->propertyAccessor = $propertyAccessor ?? PropertyAccess::createPropertyAccessor();
    }

    protected function getIriConverter(): IriConverterInterface
    {
        return $this->iriConverter;
    }

    protected function getPropertyAccessor(): PropertyAccessorInterface
    {
        return $this->propertyAccessor;
    }

    /**
     * {@inheritDoc}
     */
    protected function filterProperty(
        string $property,
        mixed $value,
        QueryBuilder $queryBuilder,
        QueryNameGeneratorInterface $queryNameGenerator,
        string $resourceClass,
        ?Operation $operation = null,
        array $context = [],
    ): void {
        if (
            null === $value
            || $property !== $this->searchParameterName
        ) {
            return;
        }

        $alias = $queryBuilder->getRootAliases()[0];
        $ors = [];
        $count = 0;

        foreach (($this->getProperties() ?? []) as $prop => $caseSensitive) {
            $filter = $this->generatePropertyOrWhere(
                $queryBuilder,
                $queryNameGenerator,
                $alias,
                $prop,
                $value,
                $resourceClass,
                $count,
                $caseSensitive ?? false,
            );

            if (null === $filter) {
                continue;
            }

            [$expr, $exprParams] = $filter;
            $ors[] = $expr;

            $queryBuilder->setParameter($exprParams[1], $exprParams[0]);

            ++$count;
        }

        $queryBuilder->andWhere($queryBuilder->expr()->orX(...$ors));
    }

    protected function generatePropertyOrWhere(
        QueryBuilder $queryBuilder,
        QueryNameGeneratorInterface $queryNameGenerator,
        string $alias,
        string $property,
        string $value,
        string $resourceClass,
        int $key,
        bool $caseSensitive = false,
    ): ?array {
        if (
            !$this->isPropertyEnabled($property, $resourceClass)
            || !$this->isPropertyMapped($property, $resourceClass, true)
        ) {
            return null;
        }

        $field = $property;
        $associations = [];

        if ($this->isPropertyNested($property, $resourceClass)) {
            [$alias, $field, $associations] = $this->addJoinsForNestedProperty(
                $property,
                $alias,
                $queryBuilder,
                $queryNameGenerator,
                $resourceClass,
                Join::INNER_JOIN,
            );
        }

        $metadata = $this->getNestedMetadata($resourceClass, $associations);

        if (
            'id' === $field
            || !$metadata->hasField($field)
        ) {
            return null;
        }

        $wrapCase = $this->createWrapCase($caseSensitive);
        $valueParameter = ':' . $queryNameGenerator->generateParameterName($field);
        $aliasedField = sprintf('%s.%s', $alias, $field);
        $keyValueParameter = sprintf('%s_%s', $valueParameter, $key);

        return [
            $queryBuilder->expr()->like(
                $wrapCase($aliasedField),
                $wrapCase((string) $queryBuilder->expr()->concat("'%'", $keyValueParameter, "'%'")),
            ),
            [$caseSensitive ? $value : strtolower($value), $keyValueParameter],
        ];
    }

    protected function createWrapCase(bool $caseSensitive): Closure
    {
        return static function (string $expr) use ($caseSensitive): string {
            if ($caseSensitive) {
                return $expr;
            }

            return sprintf('LOWER(%s)', $expr);
        };
    }

    /**
     * {@inheritDoc}
     */
    protected function getType(string $doctrineType): string
    {
        return 'string';
    }

    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,
                'description' => 'Recherche sur les propriétés spécifiées.',
            ],
        ];
    }
}

    ApiFilter(
        SearchMultiFieldsFilter::class,
        properties: [
            'name',
            'email',
            'manager.fullname',
            'manager.email',
        ],
    ),

@loicngr This is absolutely on point, thanks !!

@aesislabs you're welcome

@loicngr please that is work only for string what i do if i want to search with int

Thank you very much! @loicngr

Here's another exemple using APIP 4

https://gist.github.com/nicolasbonnici/3d598c8981ec1ac9769b4b7f5a9031f6

<?php

namespace App\Filter;

use ApiPlatform\Doctrine\Orm\Filter\FilterInterface;
use Doctrine\ORM\Query\Expr\Comparison;
use Doctrine\ORM\QueryBuilder;
use ApiPlatform\Doctrine\Orm\Util\QueryNameGeneratorInterface;
use Symfony\Component\HttpFoundation\RequestStack;
use Symfony\Component\PropertyInfo\Type;
use Symfony\Component\Serializer\NameConverter\CamelCaseToSnakeCaseNameConverter;

class SearchOrFilter implements FilterInterface
{
    private ?CamelCaseToSnakeCaseNameConverter $nameConverter = null;

    public function __construct(private readonly RequestStack $requestStack, private readonly array $properties = [])
    {
    }

    private function getNameConverter(): CamelCaseToSnakeCaseNameConverter
    {
        return $this->nameConverter ??= new CamelCaseToSnakeCaseNameConverter();
    }

    public function apply(
        QueryBuilder $queryBuilder,
        QueryNameGeneratorInterface $queryNameGenerator,
        string $resourceClass,
        $operation = null,
        array $context = []
    ): void
    {
        $request = $this->requestStack->getCurrentRequest();
        if (!$request) {
            return;
        }

        $alias = $queryBuilder->getRootAliases()[0];
        $orConditions = [];

        foreach ($this->properties as $property => $settings) {
            $type = is_array($settings) ? $settings['type'] : $settings;
            $value = $request->get($this->getNameConverter()->normalize($property));

            if ($value === null) {
                continue;
            }

            $parameterName = $queryNameGenerator->generateParameterName($property);

            $orConditions[] = $this->buildCondition(
                $queryBuilder,
                $alias,
                $property,
                $parameterName,
                $value,
                $type
            );
        }

        if (!empty($orConditions)) {
            $queryBuilder->andWhere($queryBuilder->expr()->orX(...$orConditions));
        }
    }

    private function buildCondition(
        QueryBuilder $queryBuilder,
        string $alias,
        string $property,
        string $parameterName,
        string $value,
        string $type
    ): Comparison {
        $isPartial = in_array($type, ['partial', 'ipartial'], true);
        $isInsensitive = in_array($type, ['ipartial', 'iexact'], true);
        $value = $isPartial ? '%' . $value . '%' : $value;

        $queryBuilder->setParameter($parameterName, $value);

        $column = $isInsensitive
            ? sprintf('LOWER(%s.%s)', $alias, $property)
            : sprintf('%s.%s', $alias, $property);
        $param = $isInsensitive
            ? sprintf('LOWER(:%s)', $parameterName)
            : sprintf(':%s', $parameterName);

        return $isPartial
            ? $queryBuilder->expr()->like($column, $param)
            : $queryBuilder->expr()->eq($column, $param);
    }

    public function getDescription(string $resourceClass): array
    {
        return array_map(fn($settings, $property) => [
            'property' => $property,
            'type' => Type::BUILTIN_TYPE_STRING,
            'required' => false,
            'description' => sprintf('Filter using OR condition on %s', $property),
        ], $this->properties, array_keys($this->properties));
    }
}