filter query on multiple fields in single query

Question

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",
 * })
 *
 * @ORM\Table(name="users")
 * @ORM\Entity(repositoryClass="Domain\Repository\UserRepository")
 * @ORM\HasLifecycleCallbacks()
 */
class User
{
  private $name;
  private $username;
  private $email;
  // ... code omitted ...
}

Answer 1

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 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 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

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

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