Skip to content

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

@anacicconi

Description

@anacicconi

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?

Activity

GregoireHebert

GregoireHebert commented on Apr 11, 2018

@GregoireHebert
Contributor

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

anacicconi commented on Apr 11, 2018

@anacicconi
Author

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

GregoireHebert commented on Apr 11, 2018

@GregoireHebert
Contributor

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

soyuka

soyuka commented on Apr 11, 2018

@soyuka
Member

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

anacicconi

anacicconi commented on Apr 11, 2018

@anacicconi
Author

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

Simperfit commented on Apr 11, 2018

@Simperfit
Contributor

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

anacicconi commented on Apr 11, 2018

@anacicconi
Author

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

teohhanhui commented on Apr 19, 2018

@teohhanhui
Contributor
anacicconi

anacicconi commented on Apr 19, 2018

@anacicconi
Author

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

anacicconi commented on Sep 5, 2018

@anacicconi
Author

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

axelvnk commented on Dec 13, 2018

@axelvnk

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

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

masseelch

masseelch commented on Jul 11, 2020

@masseelch

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

4 remaining items

metaclass-nl

metaclass-nl commented on Dec 18, 2021

@metaclass-nl

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

john-dufrene-dev commented on Dec 22, 2021

@john-dufrene-dev

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

metaclass-nl commented on Dec 23, 2021

@metaclass-nl

How do i obtian the 2.7 version of api platform ?

metaclass-nl

metaclass-nl commented on Nov 14, 2022

@metaclass-nl

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

drennvinn commented on Jan 9, 2023

@drennvinn

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

LaurineLassalle84120 commented on Feb 26, 2023

@LaurineLassalle84120

@drennvinn

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

drennvinn

drennvinn commented on Feb 26, 2023

@drennvinn

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

AmineOUERTANI commented on May 5, 2023

@AmineOUERTANI

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

metaclass-nl commented on May 7, 2023

@metaclass-nl

@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

silverbackdan commented on May 7, 2023

@silverbackdan

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

AmineOUERTANI commented on May 9, 2023

@AmineOUERTANI

@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

lcottingham commented on Aug 26, 2023

@lcottingham

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

luizkim commented on Sep 28, 2023

@luizkim

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

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

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

      Development

      No branches or pull requests

        Participants

        @teohhanhui@GregoireHebert@soyuka@Simperfit@metaclass-nl

        Issue actions

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