Skip to content

Instantly share code, notes, and snippets.

@silverbackdan
Last active July 12, 2023 09:30
Show Gist options
  • Save silverbackdan/0a1753735e07210b3f4365a3100b83b7 to your computer and use it in GitHub Desktop.
Save silverbackdan/0a1753735e07210b3f4365a3100b83b7 to your computer and use it in GitHub Desktop.
The API Platform filter will allow you to pass a query `or[field1,field2]=value` to search multiple fields for a search value. It works by using the standard API Platform SearchFilter as a basis, and wrapping all of your OR queried inside a single AND query so you can combine with other where bys
<?php
declare(strict_types=1);
namespace App\Filter;
use ApiPlatform\Core\Bridge\Doctrine\Orm\Filter\SearchFilter;
use ApiPlatform\Core\Bridge\Doctrine\Orm\Util\QueryNameGeneratorInterface;
use ApiPlatform\Core\Exception\InvalidArgumentException;
use App\Entity\Term;
use Doctrine\ORM\Query\Parameter;
use Doctrine\ORM\QueryBuilder;
class OrSearchFilter extends SearchFilter
{
protected function filterProperty(string $property, $values, QueryBuilder $queryBuilder, QueryNameGeneratorInterface $queryNameGenerator, string $resourceClass, string $operationName = null): void
{
// Just use this filter for `or` query parameter
if ($property !== 'or') {
return;
}
$queryJoinParts = [];
$ors = [];
// Loop through every time the parameter is used
// fields will be comma delimited string if used as described or[field1,field2]
foreach ($values as $fields=>$value) {
// Clone and empty the where part of the query builder
$subQueryBuilder = clone $queryBuilder;
$subQueryBuilder->resetDQLPart('where');
// create array of all fields we want to query
$orProperties = explode(',', $fields);
foreach ($orProperties as $orProperty) {
// this will include all the nice stuff that the parent class implements before using our adapted `addWhereByStrategy`
parent::filterProperty($orProperty, $value, $subQueryBuilder, $queryNameGenerator, $resourceClass, $operationName);
}
// This could result in further join queries so we should add them into our main QueryBuilder
$queryJoinParts[] = $subQueryBuilder->getDQLPart('join');
$ors[] = $subQueryBuilder->getDQLPart('where');
// Include updated parameters, we will still have parameters from the original query builder
foreach ($subQueryBuilder->getParameters() as $parameter) {
/** @var Parameter $parameter */
$queryBuilder->setParameter($parameter->getName(), $parameter->getValue(), $parameter->getType());
}
}
$queryBuilder->resetDQLPart('join');
foreach ($queryJoinParts as $joinParts) {
foreach ($joinParts as $alias=>$joins) {
foreach ($joins as $join) {
$queryBuilder->add('join', [$alias=>$join], true);
}
}
}
// Add the `or` queries we have generated into our main queryBuilder DQL parts in a single `and`
$queryBuilder->andWhere($queryBuilder->expr()->andX(...$ors));
}
/**
* This method is copied straight from the extended class, swapping `andWhere` for `orWhere`
*/
protected function addWhereByStrategy(string $strategy, QueryBuilder $queryBuilder, QueryNameGeneratorInterface $queryNameGenerator, string $alias, string $field, $values, bool $caseSensitive): void
{
if (!\is_array($values)) {
$values = [$values];
}
$wrapCase = $this->createWrapCase($caseSensitive);
$valueParameter = ':'.$queryNameGenerator->generateParameterName($field);
$aliasedField = sprintf('%s.%s', $alias, $field);
if (null === $strategy || self::STRATEGY_EXACT === $strategy) {
if (1 === \count($values)) {
$queryBuilder
->orWhere($queryBuilder->expr()->eq($wrapCase($aliasedField), $wrapCase($valueParameter)))
->setParameter($valueParameter, $values[0]);
return;
}
$queryBuilder
->orWhere($queryBuilder->expr()->in($wrapCase($aliasedField), $valueParameter))
->setParameter($valueParameter, $caseSensitive ? $values : array_map('strtolower', $values));
return;
}
$ors = [];
$parameters = [];
foreach ($values as $key => $value) {
$keyValueParameter = sprintf('%s_%s', $valueParameter, $key);
$parameters[$caseSensitive ? $value : strtolower($value)] = $keyValueParameter;
switch ($strategy) {
case self::STRATEGY_PARTIAL:
$ors[] = $queryBuilder->expr()->like(
$wrapCase($aliasedField),
$wrapCase((string) $queryBuilder->expr()->concat("'%'", $keyValueParameter, "'%'"))
);
break;
case self::STRATEGY_START:
$ors[] = $queryBuilder->expr()->like(
$wrapCase($aliasedField),
$wrapCase((string) $queryBuilder->expr()->concat($keyValueParameter, "'%'"))
);
break;
case self::STRATEGY_END:
$ors[] = $queryBuilder->expr()->like(
$wrapCase($aliasedField),
$wrapCase((string) $queryBuilder->expr()->concat("'%'", $keyValueParameter))
);
break;
case self::STRATEGY_WORD_START:
$ors[] = $queryBuilder->expr()->orX(
$queryBuilder->expr()->like($wrapCase($aliasedField), $wrapCase((string) $queryBuilder->expr()->concat($keyValueParameter, "'%'"))),
$queryBuilder->expr()->like($wrapCase($aliasedField), $wrapCase((string) $queryBuilder->expr()->concat("'% '", $keyValueParameter, "'%'")))
);
break;
default:
throw new InvalidArgumentException(sprintf('strategy %s does not exist.', $strategy));
}
}
$queryBuilder->orWhere($queryBuilder->expr()->orX(...$ors));
array_walk($parameters, [$queryBuilder, 'setParameter']);
}
}
@adiopek
Copy link

adiopek commented Jul 12, 2023

Thank you! This helped me a lot. Your solution is the best one I found. I've modified the code a bit to allow for extending SearchStrategyFilter (https://gist.github.com/teohhanhui/6d77c2ef4cbdde7ecbee9b314ad27281?permalink_comment_id=4285821#gistcomment-4285821).

I've modified it too to fallback to default or specified in config if the strategy supplied by client is invalid.

<?php

namespace App\Filter;

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

class SearchStrategyFilter extends SearchFilter
{
    protected function filterProperty(string $property, $value, QueryBuilder $queryBuilder, QueryNameGeneratorInterface $queryNameGenerator, string $resourceClass, string $operationName = null)
    {
        if (is_array($value)) {
            $first = array_key_first($value);
            if (in_array($first, [
                self::STRATEGY_EXACT,
                self::STRATEGY_PARTIAL,
                self::STRATEGY_START,
                self::STRATEGY_END,
                self::STRATEGY_WORD_START,
            ], true)) {
                $this->properties[$property] = $first;
            }
            $value = $value[$first];
            parent::filterProperty($property, $value, $queryBuilder, $queryNameGenerator, $resourceClass, $operationName);
            return;
        }
        parent::filterProperty($property, $value, $queryBuilder, $queryNameGenerator, $resourceClass, $operationName);
    }
}

I wanted to allow different values for different properties so implemented that as well.

?or[field1(exact),field2(partial)]=value1,value2

I also wanted to avoid copying addWhereByStrategy so I convert ands into ors after original addWhereByStrategy is run.

<?php

namespace App\Filter;

use ApiPlatform\Core\Bridge\Doctrine\Orm\Util\QueryNameGeneratorInterface;
use Doctrine\ORM\Query\Expr\Andx;
use Doctrine\ORM\Query\Expr\Orx;
use Doctrine\ORM\Query\Parameter;
use Doctrine\ORM\QueryBuilder;

class OrSearchFilter extends SearchStrategyFilter
{

    protected function filterProperty(string $property, $value, QueryBuilder $queryBuilder, QueryNameGeneratorInterface $queryNameGenerator, string $resourceClass, string $operationName = null)
    {
        if ($property !== 'or' || !is_array($value)) {
            return;
        }

        $queryJoinParts = [];
        /** @var Andx[] $ands */
        $ands = [];

        foreach ($value as $fields => $values) {
            // Clone and empty the where part of the query builder
            $subQueryBuilder = clone $queryBuilder;
            $subQueryBuilder->resetDQLPart('where');

            // create array of all fields we want to query
            $orProperties = explode(',', $fields);
            // create array of values
            $orValues = explode(',', $values);
            foreach ($orProperties as $index => $orPropertyWithStrategy) {
                // extract property and strategy
                $propertyAndStrategy = explode('(', $orPropertyWithStrategy);
                $orProperty = $propertyAndStrategy[0];
                $strategy = $propertyAndStrategy[1] ? substr($propertyAndStrategy[1], 0, -1) : null;
                // take value accordingly or the first one if no other supplied
                $orValue = $orValues[$index] ?? $orValues[0] ?? '';
                // prepare value to be handled by SearchStrategyFilter
                $finalOrValue = $strategy ? [$strategy => $orValue] : $orValue;
                // this will include all the nice stuff that the parent class implements
                parent::filterProperty($orProperty, $finalOrValue, $subQueryBuilder, $queryNameGenerator, $resourceClass, $operationName);
            }

            // This could result in further join queries, so we should add them into our main QueryBuilder
            $queryJoinParts[] = $subQueryBuilder->getDQLPart('join');
            $ands[] = $subQueryBuilder->getDQLPart('where');

            // Include updated parameters, we will still have parameters from the original query builder
            foreach ($subQueryBuilder->getParameters() as $parameter) {
                /** @var Parameter $parameter */
                $queryBuilder->setParameter($parameter->getName(), $parameter->getValue(), $parameter->getType());
            }
        }
        
        // change ands into ors
        /** @var OrX[] $ors */
        $ors = [];
        foreach ($ands as $and) {
            $ors[] = $queryBuilder->expr()->orX(...$and->getParts());
        }

        $queryBuilder->resetDQLPart('join');
        foreach ($queryJoinParts as $joinParts) {
            foreach ($joinParts as $alias=>$joins) {
                foreach ($joins as $join) {
                    $queryBuilder->add('join', [$alias=>$join], true);
                }
            }
        }
        // Add the `or` queries into our main queryBuilder DQL parts in a single `and`
        $queryBuilder->andWhere($queryBuilder->expr()->andX(...$ors));
    }
}

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment