API Platform "OR" and "IN" filters #639

anacicconi · 2018-04-11T07:52:31Z

I have been testing API Platform with the different filters available. I noticed we can do a lot without creating custom endpoints. However, when it comes to "OR" and "IN" filters I couldn't find anything.

The idea is to have behind queries acting as:

"WHERE property1 = foo OR property2 = bar"

or

"WHERE property1 IN (foo, bar...)"

Should we create a custom filter for each entity where we can have this kind of query or has API Platform some default feature?

GregoireHebert · 2018-04-11T08:28:17Z

Hi @anacicconi, this is something really missing right now and yes you can indeed create a custom filter at the moment.

There is an issue opened in the api-platform/core#1832 to fill the gap :)

anacicconi · 2018-04-11T08:33:06Z

Thanks for your quick reply @GregoireHebert !

Are developers creating custom endpoints for these cases? Or custom filters are indeed the best option as we can remain restful?

GregoireHebert · 2018-04-11T09:05:02Z

I would go for a custom filter. Maybe @Simperfit has a different POV on this ?

soyuka · 2018-04-11T09:57:16Z

Custom filters it is :) We already reasoned to this in another issue IIRC.

anacicconi · 2018-04-11T10:06:12Z

I think you're talking about this one @soyuka: api-platform/core#398

I read you said this feature should not be on core. However, as it was from June 2017, I wasn't sure if it was up to date.

Thanks ;)

Simperfit · 2018-04-11T10:48:24Z

Same for me, it should be in a custom filter.

But, we are maybe going to implement a where filter, if the RFC is approved. I will prepare the PR anyway ;).

anacicconi · 2018-04-11T13:45:01Z

Can I reuse the issue to ask a question about the custom filters? I have been testing them since I got your replies. Using the Regexp from the documentation, I'm able to reach the filterProperty method when I add the filter to my resource like this:

@ApiResource(attributes={"filters"={RegexpFilter::class}})

However, when I try to use the ApiFilter annotation nothing happens:

@ApiFilter(RegexpFilter::class, properties={"website"})

I tried many combinations of the way I add the filter to the resource and the way I declare my service. There are only two ways I can make this work.

First:

'App\Filter\RegexpFilter':
        arguments: [ '@doctrine', '@request_stack', '@?logger', { website: ~ } ]

@ApiResource(attributes={"filters"={RegexpFilter::class}})

This way I have both the filter in the swagger interface and I reach the filterProperty method.

Second:

'App\Filter\RegexpFilter':
        arguments: [ '@doctrine', '@request_stack', '@?logger']

@ApiResource(attributes={"filters"={RegexpFilter::class}})

This way I don't have the filter in the swagger interface but still if I curl the url with regexp_website as a parameter I reach the filterProperty method.

My service options for both cases:

Information for Service "App\Filter\RegexpFilter"
 ---------------- -------------------------
  Option           Value
 ---------------- -------------------------
  Service ID       App\Filter\RegexpFilter
  Class            App\Filter\RegexpFilter
  Tags             api_platform.filter
  Public           no
  Synthetic        no
  Lazy             no
  Shared           yes
  Abstract         no
  Autowired        yes
  Autoconfigured   yes
 ---------------- -------------------------

Even if the first option works, I would prefer to use the ApiFilter annotation. This way I would be able to declare a generic custom filter that I can use in any resource without specifying the properties in the services.yml. And of course I would have the filter in the swagger interface.

Am I missing something here?

teohhanhui · 2018-04-19T08:59:14Z

Does the multi values not work for the IN case?

https://github.com/api-platform/core/blob/v2.2.5/src/Bridge/Doctrine/Orm/Filter/SearchFilter.php#L251

anacicconi · 2018-04-19T09:22:03Z

Hi @teohhanhui, yes they work. The title of my question is not good. I was starting to deal with filters when I asked it. As you pointed out, the Search Filter checks if it is an array or a single value. If it's an array, it adds a "IN". However, for "OR" queries, I had to do a custom filter.

anacicconi · 2018-09-05T09:20:37Z

Hello,

I'll start this conversation again if it's ok. I did a custom "OR" filter for my app which is basically a copy of the "SearchFilter" with some modifications for the query. It works just fine. However, I think it's a pity to have a copy of another file like that. I was wondering if I could have my custom filter to extend the "SearchFilter". I read in one of your issues that you don't advise people to do that. Is there a reason? Besides the fact that eventually the "SearchFilter" could be updated and break my custom filter.

Moreover, would it be a good idea to change the original "SearchFilter" so the user could choose what kind of operator he wants?

Thanks!

axelvnk · 2018-12-13T15:29:25Z

In case someone is looking for an example of an OR search filter : here it is

https://gist.github.com/axelvnk/edf879af5c7dbd9616a4eeb77c7181a3

masseelch · 2020-07-11T16:31:42Z

In case someone is looking for an example of an OR search filter : here it is

https://gist.github.com/axelvnk/edf879af5c7dbd9616a4eeb77c7181a3

I did one myself (more like a full-text kind-of filter thingy).

https://gist.github.com/masseelch/47931f3a745409f8f44c69efa9ecb05c

Tersoal · 2020-10-01T09:08:30Z

In case someone is looking for an example of an OR search filter : here it is
https://gist.github.com/axelvnk/edf879af5c7dbd9616a4eeb77c7181a3

I did one myself (more like a full-text kind-of filter thingy).

https://gist.github.com/masseelch/47931f3a745409f8f44c69efa9ecb05c

I've added multiple search options and swagger doc.

https://gist.github.com/Tersoal/d45b0cc75cadf72cd7c0e49b892809b3

silverbackdan · 2020-12-31T14:41:07Z

Here's another example filter you could implement as a temporary workaround extending the SearchFilter while decisions are made on which where filter syntax to implement (#1724) https://gist.github.com/silverbackdan/0a1753735e07210b3f4365a3100b83b7 and using the syntax or[field1,field2]=search_value

metaclass-nl · 2021-12-18T16:44:07Z

One can accomplish similar results (and more) with FilterLogic: combines existing API Platform ORM Filters with AND, OR and NOT according to client request.

supports nested logic (parentheses)
supports multiple criteria for the same property
existing requests keep working unmodified if not using "and", "or" or "not" as query parameters
workarounds are included for combining filtering by nested properties through OR (does modify the outcome of existing requests)

john-dufrene-dev · 2021-12-22T19:02:19Z

Hi,

(Sorry for my bad english)

With version 2.7 somes changes are applied,
Someone have an example with 2.7 version of api platform ?

Thank's in advance !

John,

metaclass-nl · 2021-12-23T12:01:08Z

How do i obtian the 2.7 version of api platform ?

metaclass-nl · 2022-11-14T10:37:57Z

Version v3.0.0.rc2 of FilterLogic has been adapted for API Platform Core 3.0 and 2.7 with metadata_backward_compatibility_layer set to false.

BTW, According to https://github.com/api-platform/api-platform/releases there is still no 2.7 version, but let's assume you meant the Core repo (but then this issues should have been created there) then still, the first 2.7 version on https://github.com/api-platform/core/releases is v2.7.0-rc.1 and it is dated jul 20 2022.

drennvinn · 2023-01-09T14:23:25Z

if it can help anyone, i wrote this in order to be able to search on multiple fields with only one query param:

    protected function filterProperty(
        string $property,
        $value,
        QueryBuilder $queryBuilder,
        QueryNameGeneratorInterface $queryNameGenerator,
        string $resourceClass,
        Operation $operation = null,
        array $context = []
    ): void
    {
        if ($property !== 'search') {
            return;
        }

        $fields = $this->getProperties();
        if (empty($fields)) {
            throw new \InvalidArgumentException('At least one field must be specified.');
        }

        $orExpressions = [];
        foreach (array_keys($fields) as $field) {
            if (!$this->isPropertyEnabled($field, $resourceClass) || !$this->isPropertyMapped($field, $resourceClass)) {
                return;
            }
            $orExpressions[] = sprintf('%s.%s LIKE :search', $queryBuilder->getRootAliases()[0], $field);
        }

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

Now you can add #[ApiFilter(MultipleFieldsSearchFilter::class, properties: ["firstName", "lastName", "TheFieldYouWant"])] in your entity 👍

LaurineLassalle84120 · 2023-02-26T07:32:10Z

@drennvinn

Can you tell me where i can use your code? In which class? In which file? I don't understand

drennvinn · 2023-02-26T13:33:53Z

Sure! here is an improved version which also allows you to search in the subresources.

// src/Filter/MultipleFieldsSearchFilter.php

namespace App\Filter;

use ApiPlatform\Doctrine\Orm\Filter\AbstractFilter;
use ApiPlatform\Doctrine\Orm\Util\QueryNameGeneratorInterface;
use ApiPlatform\Metadata\Operation;

use Doctrine\ORM\QueryBuilder;

use Symfony\Component\PropertyInfo\Type;


final class MultipleFieldsSearchFilter extends AbstractFilter
{
    protected function filterProperty(
        string $property,
        $value,
        QueryBuilder $queryBuilder,
        QueryNameGeneratorInterface $queryNameGenerator,
        string $resourceClass,
        Operation $operation = null,
        array $context = []
    ): void
    {
        if ($property !== 'search') {
            return;
        }

        $fields = $this->getProperties();
        if (empty($fields)) {
            throw new \InvalidArgumentException('At least one field must be specified.');
        }

        $alias = $queryBuilder->getRootAliases()[0];
        $orExpressions = [];
        foreach (array_keys($fields) as $k => $field) {
            if ($this->isPropertyNested($field, $resourceClass)) {
                $exploded_field = explode('.', $field);
                if (!in_array($exploded_field[0], $queryBuilder->getAllAliases())) {
                    $queryBuilder->leftJoin($alias . '.' . $exploded_field[0], $exploded_field[0]);
                }
                $orExpressions[] = sprintf('%s.%s LIKE :search', $exploded_field[0], $exploded_field[1]);
            } else {
                $orExpressions[] = sprintf('%s.%s LIKE :search', $alias, $field);
            }
        }

        $queryBuilder
            ->andWhere(implode(' OR ', $orExpressions))
            ->setParameter('search', "%$value%");

    }


    public function getDescription(string $resourceClass): array
    {
        // ...
    }
}

And on the entity side you can declare it as follows

// src/Entity/Book.php

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;
use ApiPlatform\Metadata\ApiResource;
use ApiPlatform\Metadata\ApiFilter;
use App\Filter\MultipleFieldsSearchFilter;
// ...


#[ApiResource(
    // ...
)]
#[ApiFilter(MultipleFieldsSearchFilter::class, properties: [
    "name",
    "description",
    "subject.name", "subject.description",
    // the other desired fields 
])]
#[ORM\Entity(repositoryClass: BookRepository::class)]
class Book
{
    // ...
}

now you can search in multiple fields with the following query: books?search=searched string in declared fields..
I think this code can be improved, but I don't have much time at the moment.

AmineOUERTANI · 2023-05-05T15:47:42Z

This code may help you solve the problem

Exemple for entity user : #[ApiFilter(OrSearchFilter::class, properties: ['firstname', 'lastname', 'country.name'])]
Uri : /api/user?search=criteria

<?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 Symfony\Component\PropertyInfo\Type;

final class OrSearchFilter extends AbstractFilter
{
    private const FILTER_KEY = 'search';

    protected function filterProperty(
        string $property,
        $value,
        QueryBuilder $queryBuilder,
        QueryNameGeneratorInterface $queryNameGenerator,
        string $resourceClass,
        Operation $operation = null,
        array $context = []
    ): void {

        if ($property !== self::FILTER_KEY) {
            return;
        }
        foreach ($this->getProperties() as $property => $stat) {

            if (!$this->isPropertyEnabled($property, $resourceClass) || !$this->isPropertyMapped($property, $resourceClass, true)) {
                return;
            }

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

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

            $queryBuilder
                ->orWhere(sprintf('%s.%s Like :search', $alias, $field))
                ->setParameter('search', "%$value%");
        }
    }

    /** {@inheritdoc}  */
    public function getDescription(string $resourceClass): array
    {
        if (!$this->properties) {
            return [];
        }

        $description = [];
        foreach ($this->properties as $property => $strategy) {
            $description[self::FILTER_KEY . "_" . $property] = [
                'property' => $property,
                'type' => Type::BUILTIN_TYPE_STRING,
                'required' => false,
                'description' => 'Filter by ' . $property . ' using Or condition',
            ];
        }

        return $description;
    }
}

metaclass-nl · 2023-05-07T11:28:42Z

@AmineOUERTANI as described in this issue using $queryBuilder->orWhere can cause security issues in combination with extensions. The solution of @drennvinn uses seperate or expressions that are combined with the rest of the query through ->andWhere so will not have this problem.

silverbackdan · 2023-05-07T17:25:49Z

As I posted above a while ago, this was also another solution I had whereby the 'or' queries are all wrapped within an 'andWhere' - I think it still works.

https://gist.github.com/silverbackdan/0a1753735e07210b3f4365a3100b83b7

AmineOUERTANI · 2023-05-09T09:09:50Z

@metaclass-nl here is the solution with andwhere

<?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 Symfony\Component\PropertyInfo\Type;

final class OrSearchFilter extends AbstractFilter
{
    private const FILTER_KEY = 'search';

    protected function filterProperty(
        string $property,
        $value,
        QueryBuilder $queryBuilder,
        QueryNameGeneratorInterface $queryNameGenerator,
        string $resourceClass,
        Operation $operation = null,
        array $context = []
    ): void {

        if ($property !== self::FILTER_KEY) {
            return;
        }
        $orExpressions = [];
        foreach ($this->getProperties() as $field => $strategy) {
            if (!$this->isPropertyEnabled($field, $resourceClass) || !$this->isPropertyMapped($field, $resourceClass, true)) {
                return;
            }

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

            $metadata = $this->getNestedMetadata($resourceClass, $associations);
            if ($metadata->hasField($field)) {
                $orExpressions[] = sprintf('%s.%s LIKE :search', $alias, $field);
            }
        }
        $queryBuilder
            ->andWhere(implode(' OR ', $orExpressions))
            ->setParameter('search', "%$value%");
    }

    /** {@inheritdoc}  */
    public function getDescription(string $resourceClass): array
    {
        if (!$this->properties) {
            return [];
        }

        $description = [];
        foreach ($this->properties as $property => $strategy) {
            $description[self::FILTER_KEY . "_" . $property] = [
                'property' => $property,
                'type' => Type::BUILTIN_TYPE_STRING,
                'required' => false,
                'description' => 'Filter by ' . $property . ' using Or condition',
            ];
        }

        return $description;
    }
}

lcottingham · 2023-08-26T22:41:57Z

Do any of these solutions work with GraphQL? Out of the box, they are detected by the graphql schema, but don't appear to be effective on query.

i.e.

query {
  books (
      search_author: "pedro pascal"
      search_publisher: "penguin"
  ) {
      ...
    }
}

the above in gql would return the query as if the search_ prefixed filters weren't there.

luizkim · 2023-09-28T20:32:56Z

In file: api\config\packages\api_platform.yaml

Remove:

parameters:
api_platform:
#metadata_backward_compatibility_layer: false

Just comment this line and the problem is gone: metadata_backward_compatibility_layer

Simperfit added the enhancement label Apr 13, 2018

anacicconi mentioned this issue Sep 6, 2018

Why does api platform force an INNER JOIN? #826

Closed

teohhanhui mentioned this issue Dec 19, 2018

[RFC] Filter composition api-platform/core#2400

Open

soyuka mentioned this issue Aug 12, 2020

[Rest] NumericFilter and RangeFilter on the same property triggers an error api-platform/core#3634

Closed

metaclass-nl mentioned this issue Dec 14, 2021

Does this work with Collections? metaclass-nl/filter-bundle#2

Closed

metaclass-nl mentioned this issue Dec 23, 2021

Built-in OR Filter #2014

Closed

soyuka closed this as completed Sep 5, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

API Platform "OR" and "IN" filters #639

API Platform "OR" and "IN" filters #639

anacicconi commented Apr 11, 2018

GregoireHebert commented Apr 11, 2018

anacicconi commented Apr 11, 2018

GregoireHebert commented Apr 11, 2018

soyuka commented Apr 11, 2018

anacicconi commented Apr 11, 2018

Simperfit commented Apr 11, 2018 •

edited

anacicconi commented Apr 11, 2018 •

edited by Simperfit

teohhanhui commented Apr 19, 2018

anacicconi commented Apr 19, 2018

anacicconi commented Sep 5, 2018 •

edited

axelvnk commented Dec 13, 2018

masseelch commented Jul 11, 2020

Tersoal commented Oct 1, 2020

silverbackdan commented Dec 31, 2020

metaclass-nl commented Dec 18, 2021

john-dufrene-dev commented Dec 22, 2021 •

edited

metaclass-nl commented Dec 23, 2021

metaclass-nl commented Nov 14, 2022

drennvinn commented Jan 9, 2023 •

edited

LaurineLassalle84120 commented Feb 26, 2023 •

edited

drennvinn commented Feb 26, 2023 •

edited

AmineOUERTANI commented May 5, 2023 •

edited

metaclass-nl commented May 7, 2023

silverbackdan commented May 7, 2023

AmineOUERTANI commented May 9, 2023 •

edited

lcottingham commented Aug 26, 2023

luizkim commented Sep 28, 2023

API Platform "OR" and "IN" filters #639

API Platform "OR" and "IN" filters #639

Comments

anacicconi commented Apr 11, 2018

GregoireHebert commented Apr 11, 2018

anacicconi commented Apr 11, 2018

GregoireHebert commented Apr 11, 2018

soyuka commented Apr 11, 2018

anacicconi commented Apr 11, 2018

Simperfit commented Apr 11, 2018 • edited

anacicconi commented Apr 11, 2018 • edited by Simperfit

teohhanhui commented Apr 19, 2018

anacicconi commented Apr 19, 2018

anacicconi commented Sep 5, 2018 • edited

axelvnk commented Dec 13, 2018

masseelch commented Jul 11, 2020

Tersoal commented Oct 1, 2020

silverbackdan commented Dec 31, 2020

metaclass-nl commented Dec 18, 2021

john-dufrene-dev commented Dec 22, 2021 • edited

metaclass-nl commented Dec 23, 2021

metaclass-nl commented Nov 14, 2022

drennvinn commented Jan 9, 2023 • edited

LaurineLassalle84120 commented Feb 26, 2023 • edited

drennvinn commented Feb 26, 2023 • edited

AmineOUERTANI commented May 5, 2023 • edited

metaclass-nl commented May 7, 2023

silverbackdan commented May 7, 2023

AmineOUERTANI commented May 9, 2023 • edited

lcottingham commented Aug 26, 2023

luizkim commented Sep 28, 2023

Simperfit commented Apr 11, 2018 •

edited

anacicconi commented Apr 11, 2018 •

edited by Simperfit

anacicconi commented Sep 5, 2018 •

edited

john-dufrene-dev commented Dec 22, 2021 •

edited

drennvinn commented Jan 9, 2023 •

edited

LaurineLassalle84120 commented Feb 26, 2023 •

edited

drennvinn commented Feb 26, 2023 •

edited

AmineOUERTANI commented May 5, 2023 •

edited

AmineOUERTANI commented May 9, 2023 •

edited