Add OrSearchFilter - filter on multiple conditions using OR #398

WybrenKoelmans · 2016-02-01T14:58:30Z

https://gist.github.com/WybrenKoelmans/81179b07ae0a10de30ba

I unfortunately do not have the time right now to make a proper PR, but using the gist above it should be straight forward what I intend.

The most important lines are
https://gist.github.com/WybrenKoelmans/81179b07ae0a10de30ba#file-orsearchfilter-php-L153

and the switch changes around
https://gist.github.com/WybrenKoelmans/81179b07ae0a10de30ba#file-orsearchfilter-php-L171

  resource.object.search_filter:
      parent: 'myapi.doctrine.orm.or_search_filter'
      arguments:
        - 'entity.user.name_first': 'partial'
          'entity.user.name_last':  'partial'
          'entity.user.telephone':  'partial'

Should give me something like

AND (entity.user.name_first LIKE '%testing%' OR
         entity.user.name_last LIKE '%testing%' OR
         entity.user.telephone LIKE '%testing%')

theofidry · 2016-02-15T11:19:48Z

IMO if we add support for the or operator we should add support for operators or, and, gt(e), lt(e) and such.

@dunglas if we take this path it would be worth taking some work from LoopBackApiBundle. But this will require some work to be fair. Would you want to do it for v2 or v1 too?

dunglas · 2016-02-15T13:12:13Z

@theofidry ok for me to include LoopBackApiBundle in core. Will you target the v1.1 or the v2?

WybrenKoelmans · 2016-02-15T13:18:14Z

Excellent news, this looks promising. Thank you both.

theofidry · 2016-02-15T13:33:20Z

@dunglas not sure I'll look more deeply this weekend. Basically I have two solutions:

Finish the support of search on multi-values for the filters and then just merge it to DunglasApiBundle as a brand new filter
Change the existing filter to ship with the new features

The 2. is very unlikely without any BC. I need to check it a bit more to be able to tell the easiest path. But it requires a bit of work to finish it, and much more time to ease the integration and check the performance impact.

dunglas · 2016-02-15T15:59:02Z

BC means 2.0 (IMO it would be better to have it in 2.0 as 1.1 is very stable now).

theofidry · 2016-02-15T20:14:30Z

Yep, 1. possibly means merge-able in v1, 2. guaranteed to go be in v2.

I'm gonna take a proper look at that this weekend (too busy this week~)

soyuka · 2016-02-16T15:25:59Z

@theofidry IMO having a case insensitive filter (#384) would concern the same topic, filters. The #384 proposal might not be adequate in the way it's configurated (ipartial instead of partial + a boolean). Let me know if I can help.

theofidry · 2016-02-16T15:31:14Z

Good reminder, will look into that as well :)

Simperfit · 2016-08-28T17:44:30Z

@theofidry what is missing on this ?

mcanepa · 2017-02-22T17:37:44Z

I'm in the need for this feature in my API; I have to search for a given value in several fields at the same time, so an OR filter will be very much welcome.

Is there any progress on this? some estimated release date?

teohhanhui · 2017-02-23T04:25:05Z

@mcanepa:

I have to search for a given value in several fields at the same time

Best use a full-text search implementation then. If you're using PostgreSQL, the built-in Full Text Search is a good option.

There are certainly use cases for an OrFilter, I just don't think what you've described is one of them.

soyuka · 2017-02-23T11:08:50Z

I agree about the Full Text Search, I've myself implemented a Filter with Oracle Text.

Anyway, you should try to build your own filter. There is a nice example here. You can also check the documentation about filters here and here.

Simperfit · 2017-06-14T13:23:38Z

Are we going to implement it on core @api-platform/core-team

soyuka · 2017-06-14T13:34:57Z

IMO it's not really worth the work (because this is never only a matter of AND or OR). We should advise to build custom filters if someone needs this.

Simperfit · 2017-06-14T13:35:58Z

i'm -1 on this too, we could help people building their custom one, but that will not be in core :)

hbroer · 2017-07-22T13:41:53Z

Ty for helping on this. Now i know how to use this simple basic functionality with 6 Lines of Configuration. Not. Now I am building a own filter. That regex example helps very well. Not. ^^ just saying ^^

hbroer · 2017-07-22T15:32:47Z

<?php

namespace AppBundle\Filter;

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

use Doctrine\Common\Annotations\AnnotationReader;

final class SearchFilter extends AbstractFilter
{
    protected function filterProperty(string $property, $value, QueryBuilder $queryBuilder, QueryNameGeneratorInterface $queryNameGenerator, string $resourceClass, string $operationName = null)
    {
        if ($property === 'search') {
            $this->logger->info('Search for: ' . $value);
        } else {
            return;
        }

        $reader = new AnnotationReader();
        $annotation = $reader->getClassAnnotation(new \ReflectionClass(new $resourceClass), 'AppBundle\\Filter\\SearchAnnotation');

        if (!$annotation) {
            throw new \HttpInvalidParamException('No Search implemented.');
        }

        $parameterName = $queryNameGenerator->generateParameterName($property);
        $search = [];

        foreach ($annotation->fields as $field)
        {
            $search[] = "o.{$field} LIKE :{$parameterName}";
        }

        $queryBuilder->andWhere(implode(' OR ', $search));
        $queryBuilder->setParameter($parameterName, '%' . $value . '%');
    }

    /**
     * @param string $resourceClass
     * @return array
     */
    public function getDescription(string $resourceClass): array
    {
        $description['search'] = [
            'property' => 'search',
            'type' => 'string',
            'required' => false,
            'swagger' => ['description' => 'Searchfilter'],
        ];

        return $description;
    }
}

<?php

namespace AppBundle\Filter;

use Doctrine\Common\Annotations\Annotation;
use Doctrine\Common\Annotations\Annotation\Target;
use Doctrine\Common\Annotations\AnnotationException;

/**
 * @Annotation
 * @Target("CLASS")
 */
final class SearchAnnotation
{
    public $fields = [];

    /**
     * Constructor.
     *
     * @param array $data Key-value for properties to be defined in this class.
     * @throws AnnotationException
     */
    public function __construct(array $data)
    {
        if (!isset($data['value']) || !is_array($data['value'])) {
            throw new AnnotationException('Options must be a array of strings.');
        }

        foreach ($data['value'] as $key => $value) {
            if (is_string($value)) {
                $this->fields[] = $value;
            } else {
                throw new AnnotationException('Options must be a array of strings.');
            }
        }
    }
}

services.yml

    'AppBundle\Filter\SearchFilter':
        class: 'AppBundle\Filter\SearchFilter'
        autowire: true
        tags: [ { name: 'api_platform.filter', id: 'search' } ]

src/AppBundle/Entity/Product.php

<?php

namespace AppBundle\Entity;
[...]
use AppBundle\Filter\SearchAnnotation as Searchable;

/**
 * [...]
 * @Searchable({"name", "description", "whatever"})
 */
class Product { [...] }

benjaminrau · 2017-11-30T13:19:34Z

Great work @hbroer, that helped me a lot. I changed few things for my usecase:

annotation now supported properties on relations
description in swagger documentation contains info which properties are searched
search is now case-insensitive

src/AppBundle/Filter/SearchFilter.php

<?php

namespace AppBundle\Filter;

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

use Doctrine\Common\Annotations\AnnotationReader;

final class SearchFilter extends AbstractFilter
{
    protected function filterProperty(string $property, $value, QueryBuilder $queryBuilder, QueryNameGeneratorInterface $queryNameGenerator, string $resourceClass, string $operationName = null)
    {
        if ($property === 'search') {
            $this->logger->info('Search for: ' . $value);
        } else {
            return;
        }

        $reader = new AnnotationReader();
        $annotation = $reader->getClassAnnotation(new \ReflectionClass(new $resourceClass), \AppBundle\Filter\SearchAnnotation::class);

        if (!$annotation) {
            throw new \HttpInvalidParamException('No Search implemented.');
        }

        $parameterName = $queryNameGenerator->generateParameterName($property);
        $search = [];
        $mappedJoins = [];

        foreach ($annotation->fields as $field)
        {
            $joins = explode(".", $field);
            for ($lastAlias = 'o', $i = 0, $num = count($joins); $i < $num; $i++) {
                $currentAlias = $joins[$i];
                if ($i === $num - 1) {
                    $search[] = "LOWER({$lastAlias}.{$currentAlias}) LIKE LOWER(:{$parameterName})";
                } else {
                    $join = "{$lastAlias}.{$currentAlias}";
                    if (false === array_search($join, $mappedJoins)) {
                        $queryBuilder->leftJoin($join, $currentAlias);
                        $mappedJoins[] = $join;
                    }
                }

                $lastAlias = $currentAlias;
            }
        }

        $queryBuilder->andWhere(implode(' OR ', $search));
        $queryBuilder->setParameter($parameterName, '%' . $value . '%');
    }

    /**
     * @param string $resourceClass
     * @return array
     */
    public function getDescription(string $resourceClass): array
    {
        $reader = new AnnotationReader();
        $annotation = $reader->getClassAnnotation(new \ReflectionClass(new $resourceClass), \AppBundle\Filter\SearchAnnotation::class);

        $description['search'] = [
            'property' => 'search',
            'type' => 'string',
            'required' => false,
            'swagger' => ['description' => 'FullTextFilter on ' . implode(', ', $annotation->fields)],
        ];

        return $description;
    }
}

iCodr8 · 2018-01-31T19:46:36Z

Thank you so much @hbroer. This is exactly what I was looking for. At first I didn't got it working, because the filter definition in the @ApiResource annotation was missing.

So I have added this and it works:

/**
 * @ApiResource(
 *     attributes={
 *         "filters"={"search"}
 *     }
 * )
 * @Searchable({"name", "description", "whatever"})
 */
class Product { [...] }

And I optimized the search. So you can enter ones evi when you are searching Kevin Jones
over multiple fields.

final class SearchFilter extends AbstractFilter
{
    // ... other code

    protected function filterProperty(string $property, $value, QueryBuilder $queryBuilder, QueryNameGeneratorInterface $queryNameGenerator, string $resourceClass, string $operationName = null)
    {
        if ($property === 'search') {
            $this->logger->info('Search for: ' . $value);
        } else {
            return;
        }

        $reader = new AnnotationReader();
        $annotation = $reader->getClassAnnotation(new \ReflectionClass(new $resourceClass), 'AppBundle\\Filter\\SearchAnnotation');

        if (!$annotation) {
            throw new \HttpInvalidParamException('No Search implemented.');
        }

        $parameterName = $queryNameGenerator->generateParameterName($property);
        $searchItems = explode(' ', str_replace('-', ' ', $value));

        if (is_array($searchItems)) {
            $andx = $queryBuilder->expr()->andx();

            foreach ($searchItems as $index => $searchItem) {
                $orx = $queryBuilder->expr()->orx();

                foreach ($annotation->fields as $field) {
                    $orx->add($queryBuilder->expr()->like('o.' . $field, ':' . $parameterName . '_' . $index));
                }

                if ($orx->count()) {
                    $queryBuilder->setParameter($parameterName . '_' . $index, '%' . $searchItem . '%');
                    $andx->add($orx);
                }
            }

            if ($andx->count()) {
                $queryBuilder->andWhere($andx);
            }
        }
    }

    // ... other code
}

apfz · 2018-03-19T21:55:12Z

I am doing the above but the filterProperty method is never reached. Could anyone have a look at my detailed question regarding this at: https://stackoverflow.com/questions/49372971/custom-filter-api-platform-not-working

zakjakub · 2018-08-14T06:10:05Z

I have same problem as @apfz - filter appears in documentation, but it's never applied. filterProperty() is never reached.

apfz · 2018-08-14T06:33:49Z

unfortunately I did not find a solution for it, so I had to switch to another API framework :(. glad I did though, filtering is now a breeze.

teohhanhui · 2018-08-17T11:56:22Z

@zakjakub @apfz Please open another issue for your question. 😄

renta · 2018-08-21T08:57:46Z

Thanks @hbroer @benjaminrau for your code. I've done a custom OR filter without an annotation https://gist.github.com/renta/b6ece3fec7896440fe52a9ec0e76571a

soyuka · 2018-08-21T09:03:04Z

looks good! Be careful with LOWER and performances though :)

dardinier · 2019-09-04T15:13:44Z

Hi @benjaminrau and @iCodr8, I liked both of your ways to do it.

But I need to combine those two features :

Search on properties :

annotation now supported properties on relations
From @benjaminrau

Search on multiples fields :

And I optimized the search. So you can enter ones evi when you are searching Kevin Jones
over multiple fields.
From @iCodr8

You both chose different ways to do it and today, I am not able to merge it.

How would you imagine it ?

teohhanhui · 2019-09-04T15:16:41Z

@dardinier Use full text search: https://www.postgresql.org/docs/10/textsearch.html

vipulw2011 · 2019-11-08T05:07:10Z

<?php

namespace AppBundle\Filter;

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

use Doctrine\Common\Annotations\AnnotationReader;

final class SearchFilter extends AbstractFilter
{
    protected function filterProperty(string $property, $value, QueryBuilder $queryBuilder, QueryNameGeneratorInterface $queryNameGenerator, string $resourceClass, string $operationName = null)
    {
        if ($property === 'search') {
            $this->logger->info('Search for: ' . $value);
        } else {
            return;
        }

        $reader = new AnnotationReader();
        $annotation = $reader->getClassAnnotation(new \ReflectionClass(new $resourceClass), 'AppBundle\\Filter\\SearchAnnotation');

        if (!$annotation) {
            throw new \HttpInvalidParamException('No Search implemented.');
        }

        $parameterName = $queryNameGenerator->generateParameterName($property);
        $search = [];

        foreach ($annotation->fields as $field)
        {
            $search[] = "o.{$field} LIKE :{$parameterName}";
        }

        $queryBuilder->andWhere(implode(' OR ', $search));
        $queryBuilder->setParameter($parameterName, '%' . $value . '%');
    }

    /**
     * @param string $resourceClass
     * @return array
     */
    public function getDescription(string $resourceClass): array
    {
        $description['search'] = [
            'property' => 'search',
            'type' => 'string',
            'required' => false,
            'swagger' => ['description' => 'Searchfilter'],
        ];

        return $description;
    }
}

<?php

namespace AppBundle\Filter;

use Doctrine\Common\Annotations\Annotation;
use Doctrine\Common\Annotations\Annotation\Target;
use Doctrine\Common\Annotations\AnnotationException;

/**
 * @Annotation
 * @Target("CLASS")
 */
final class SearchAnnotation
{
    public $fields = [];

    /**
     * Constructor.
     *
     * @param array $data Key-value for properties to be defined in this class.
     * @throws AnnotationException
     */
    public function __construct(array $data)
    {
        if (!isset($data['value']) || !is_array($data['value'])) {
            throw new AnnotationException('Options must be a array of strings.');
        }

        foreach ($data['value'] as $key => $value) {
            if (is_string($value)) {
                $this->fields[] = $value;
            } else {
                throw new AnnotationException('Options must be a array of strings.');
            }
        }
    }
}

services.yml

    'AppBundle\Filter\SearchFilter':
        class: 'AppBundle\Filter\SearchFilter'
        autowire: true
        tags: [ { name: 'api_platform.filter', id: 'search' } ]

src/AppBundle/Entity/Product.php

<?php

namespace AppBundle\Entity;
[...]
use AppBundle\Filter\SearchAnnotation as Searchable;

/**
 * [...]
 * @Searchable({"name", "description", "whatever"})
 */
class Product { [...] }

@hbroer I am beginner to api platform and I want to achieve search in multiple properties i.e OR Filter. Can you please help me configuring it.
@benjaminrau @iCodr8 If you can also, Please.
Thanks in advance

teohhanhui · 2019-11-08T17:34:53Z

@vipulw2011 Please use the proper channels for support questions. Thanks!

You can ask on the #api-platform channel of Symfony Slack.

kacperjurak · 2020-01-27T12:48:44Z

Using of ApiPlatform\Core\Bridge\Doctrine\Orm\Filter\AbstractFilter is deprecated.

Please use ApiPlatform\Core\Bridge\Doctrine\Orm\Filter\AbstractContextAwareFilter instead.

masseelch · 2020-07-11T16:33:41Z

@dardinier I think this is what you are looking for: https://gist.github.com/masseelch/47931f3a745409f8f44c69efa9ecb05c

@hbroer, @benjaminrau

khaledBou · 2020-10-07T11:17:02Z

I tried to add the custom search filter to an abstract class but it doesn't work , i can't instantiate an abstract class, is any solution to prevent the new $resourceClass ? or maybe a custom search filter for an abstract class ?

dunglas added the enhancement label Aug 19, 2016

Simperfit added the easy pick label Jun 14, 2017

Simperfit closed this as completed Jun 14, 2017

anacicconi mentioned this issue Apr 11, 2018

API Platform "OR" and "IN" filters api-platform/api-platform#639

Closed

teohhanhui added question and removed easy pick labels Aug 17, 2018

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Add OrSearchFilter - filter on multiple conditions using OR #398

Add OrSearchFilter - filter on multiple conditions using OR #398

WybrenKoelmans commented Feb 1, 2016

theofidry commented Feb 15, 2016

dunglas commented Feb 15, 2016

WybrenKoelmans commented Feb 15, 2016

theofidry commented Feb 15, 2016

dunglas commented Feb 15, 2016

theofidry commented Feb 15, 2016

soyuka commented Feb 16, 2016

theofidry commented Feb 16, 2016

Simperfit commented Aug 28, 2016

mcanepa commented Feb 22, 2017

teohhanhui commented Feb 23, 2017

soyuka commented Feb 23, 2017

Simperfit commented Jun 14, 2017

soyuka commented Jun 14, 2017

Simperfit commented Jun 14, 2017

hbroer commented Jul 22, 2017

hbroer commented Jul 22, 2017

benjaminrau commented Nov 30, 2017 •

edited by soyuka

iCodr8 commented Jan 31, 2018

apfz commented Mar 19, 2018 •

edited

zakjakub commented Aug 14, 2018

apfz commented Aug 14, 2018

teohhanhui commented Aug 17, 2018

renta commented Aug 21, 2018 •

edited

soyuka commented Aug 21, 2018

dardinier commented Sep 4, 2019

teohhanhui commented Sep 4, 2019

vipulw2011 commented Nov 8, 2019

teohhanhui commented Nov 8, 2019

kacperjurak commented Jan 27, 2020

masseelch commented Jul 11, 2020 •

edited

khaledBou commented Oct 7, 2020

Add OrSearchFilter - filter on multiple conditions using OR #398

Add OrSearchFilter - filter on multiple conditions using OR #398

Comments

WybrenKoelmans commented Feb 1, 2016

theofidry commented Feb 15, 2016

dunglas commented Feb 15, 2016

WybrenKoelmans commented Feb 15, 2016

theofidry commented Feb 15, 2016

dunglas commented Feb 15, 2016

theofidry commented Feb 15, 2016

soyuka commented Feb 16, 2016

theofidry commented Feb 16, 2016

Simperfit commented Aug 28, 2016

mcanepa commented Feb 22, 2017

teohhanhui commented Feb 23, 2017

soyuka commented Feb 23, 2017

Simperfit commented Jun 14, 2017

soyuka commented Jun 14, 2017

Simperfit commented Jun 14, 2017

hbroer commented Jul 22, 2017

hbroer commented Jul 22, 2017

benjaminrau commented Nov 30, 2017 • edited by soyuka

iCodr8 commented Jan 31, 2018

apfz commented Mar 19, 2018 • edited

zakjakub commented Aug 14, 2018

apfz commented Aug 14, 2018

teohhanhui commented Aug 17, 2018

renta commented Aug 21, 2018 • edited

soyuka commented Aug 21, 2018

dardinier commented Sep 4, 2019

teohhanhui commented Sep 4, 2019

vipulw2011 commented Nov 8, 2019

teohhanhui commented Nov 8, 2019

kacperjurak commented Jan 27, 2020

masseelch commented Jul 11, 2020 • edited

khaledBou commented Oct 7, 2020

benjaminrau commented Nov 30, 2017 •

edited by soyuka

apfz commented Mar 19, 2018 •

edited

renta commented Aug 21, 2018 •

edited

masseelch commented Jul 11, 2020 •

edited