How to integrate SearchFilter for searching in multiple properties of entity using Api-Platform?

Question

I have 4 fields in a table
ID | Name | EAN | HAN

These are properties in one of my entity So, How do I configure entity with searchFilter so that When I search a string it should check for it in all 4 columns and return the matched results.

For example :- if I search "car" it will match in name property and return but when I type id say "219" it will match in id property and return me accordingly.

I want to achieve this using api-platform only and my project is in symfony.

So, search should look in all 4 fields and then return result.

Answer 1

You can archieve this with a custom filter that combines those criteria with OR. I made one for chapter 6 Sorting and Custom Filter of my tutorial. I include its code below.

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

#[ApiFilter(filterClass: SimpleSearchFilter::class,
properties: ['ID', 'Name', 'EAN', 'HAN'])]

A query string like:

?simplesearch=car

will search all properties specified case insensitive LIKE %car% (You do need to url-encode the parameter value if you create the query string programatically)

You can also configure the parameter name in the ApiFilter tag. For example:

#[ApiFilter(filterClass: SimpleSearchFilter::class,
properties: ['ID', 'Name', 'EAN', 'HAN'])]
arguments: ['searchParameterName'='search'])]

will allow a query string like:

?search=car

I had to decide how this filter would combine with other filters. For compatibility with the existing filters i used AND. So if you for example also add a standard DateFilter to the same Entity class you will be able to search for some words in the specified fields AND after a specified date but not OR after a specified date. For example:

?simplesearch=car&produced[after]=2018-03-31

It splits the search string into words and searches each of the properties for each word, so

?simplesearch=car 219

will search in all specified properties both .. LIKE %car% OR .. LIKE %219%

Yes, it does assume that all properties are of type string, or that your database supports LIKE for other property types (MySQL does, Postgres does not, so if ID is an int, this will not work with Postgres). The filter does sort by relevance (that's why it's called SimpleSearchFilter).

Of course you can change the code at will, adding different handling for properties with other data types will not be too hard if you know how to use the metadata from Doctrine.

Here is the code (for apip 3.0):

<?php

namespace App\Filter;

use ApiPlatform\Doctrine\Orm\Filter\AbstractFilter;
use ApiPlatform\Doctrine\Orm\Util\QueryNameGeneratorInterface;
use ApiPlatform\Metadata\Operation;
use Doctrine\ORM\Query\Expr\Join;
use Doctrine\ORM\QueryBuilder;
use Doctrine\Persistence\ManagerRegistry;
use Psr\Log\LoggerInterface;
use Symfony\Component\Serializer\NameConverter\NameConverterInterface;
use ApiPlatform\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. Nested properties are supported.
 * @package App\Filter
 */
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 https://stackoverflow.com/users/8451711/hasbert and https://stackoverflow.com/users/11599673/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',
                ]
            ]
        ];
    }
}

Due to the constructor argument "searchParameterName" the service needs configuration in api/config/services.yaml

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