Unmapped filters, is there a proper way instead of a workaround?

[thomas-l]

I was satisfied to find exactly what I needed in the doc, filtering on a property from a related entity: Filters > Unmapped Filters. The ->mapped() method does not exist but you can replace it easily with ->setFormTypeOption('mapped', false). I've managed to make it work with a workaround, but I would really prefer with a clean custom filter! Could anyone give me a hint?

Here is my workaround, with the Order -> Customer -> Country example from the doc:

// src/Controller/Admin/OrderCrudController.php

public function configureFilters(Filters $filters): Filters
{
    $filters
        ->add(EntityFilter::new('country', 'country.label')
            ->setFormTypeOption('value_type_options.class', Country::class)
            ->setFormTypeOption(
                'value_type_options.query_builder',
                static fn(EntityRepository $repository) => $repository
                    ->createQueryBuilder('country')
                    // ...
                    ->orderBy('country.name', 'ASC'))
            ->setFormTypeOption('value_type_options.multiple', true))
    ;
}

public function createIndexQueryBuilder(
    SearchDto $searchDto,
    EntityDto $entityDto,
    FieldCollection $fields,
    FilterCollection $filters
): QueryBuilder {
    $countries = [];
        
    if (isset($searchDto->getAppliedFilters()['country'])) {
        $appliedFilters = $searchDto->getAppliedFilters();
        $countries = $appliedFilters['country']['value'];
        unset($appliedFilters['country']);
        $searchDto = new SearchDto(
            $searchDto->getRequest(),
            $searchDto->getSearchableProperties(),
            $searchDto->getQuery(),
            [],
            $searchDto->getSort(),
            $appliedFilters
        );
    }
    
    $qb = parent::createIndexQueryBuilder($searchDto, $entityDto, $fields, $filters);

    if (count($countries)) {
        $qb->join('entity.customer', 'customer')
            ->andWhere('customer.country IN (:countries)')
            ->setParameter('countries', $countries);
    }

    return $qb;
}

[vic-blt]

How I would handle it:

• Create a custom filter src/Filter/CountryFilter.php

<?php

namespace App\Filter;

use App\Entity\Country;
use App\Entity\Customer;
use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\Query\Expr;
use Doctrine\ORM\QueryBuilder;
use EasyCorp\Bundle\EasyAdminBundle\Dto\EntityDto;
use EasyCorp\Bundle\EasyAdminBundle\Dto\FieldDto;
use EasyCorp\Bundle\EasyAdminBundle\Dto\FilterDataDto;
use EasyCorp\Bundle\EasyAdminBundle\Filter\FilterTrait;
use EasyCorp\Bundle\EasyAdminBundle\Form\Filter\Type\EntityFilterType;

class CountryFilter
{
    use FilterTrait;

    public static function new(string $propertyName, $label = null): self
    {
        return (new self())
            ->setFilterFqcn(__CLASS__)
            ->setProperty($propertyName)
            ->setLabel($label)
            ->setFormType(EntityFilterType::class)
            ->setFormTypeOption('value_type_options', [
                'class' => Country::class,
                'multiple' => true,
                'query_builder' => function (EntityRepository $repository) {
                    return $repository
                        ->createQueryBuilder('country')
                        ->orderBy('country.name', 'ASC');
                }
            ]);
    }

    public function apply(QueryBuilder $queryBuilder, FilterDataDto $filterDataDto, ?FieldDto $fieldDto, EntityDto $entityDto): void
    {
        $alias = 'customer';
        $property = $filterDataDto->getProperty();
        $comparison = $filterDataDto->getComparison();
        $parameterName = $filterDataDto->getParameterName();
        $countries = $filterDataDto->getValue();

        $queryBuilder
            ->innerJoin(Customer::class, $alias, Expr\Join::WITH, 'entity.customer = customer')
            ->andWhere(sprintf('%s.%s %s :%s', $alias, $property, $comparison, $parameterName))
            ->setParameter($parameterName, $countries);
    }
}

• Add this filter in src/Controller/Admin/OrderCrudController.php

<?php

namespace App\Controller\Admin;

use App\Filter\CountryFilter;
use EasyCorp\Bundle\EasyAdminBundle\Config\Filters;
use EasyCorp\Bundle\EasyAdminBundle\Controller\AbstractCrudController;
// ...

class OrderCrudController extends AbstractCrudController
{
    // ...
    public function configureFilters(Filters $filters): Filters
    {
        return $filters->add(CountryFilter::new('country'));
    }
    // ...
}

[thomas-l]

Thank you very much! That seems so obvious now, I was probably too tired to see clear... Anyway, I'll implement it later and tell you what.

[cubaguest]

My universal filter. It's not the best, but works for me.

<?php

namespace App\Filter;

use Doctrine\ORM\Query\Expr;
use Doctrine\ORM\QueryBuilder;
use EasyCorp\Bundle\EasyAdminBundle\Contracts\Filter\FilterInterface;
use EasyCorp\Bundle\EasyAdminBundle\Dto\EntityDto;
use EasyCorp\Bundle\EasyAdminBundle\Dto\FieldDto;
use EasyCorp\Bundle\EasyAdminBundle\Dto\FilterDataDto;
use EasyCorp\Bundle\EasyAdminBundle\Filter\FilterTrait;
use EasyCorp\Bundle\EasyAdminBundle\Form\Filter\Type\TextFilterType;

class AssociationFilter implements FilterInterface
{
   use FilterTrait;

   protected $alias;
   protected $joinClass;

   public static function new(string $propertyName, $label = null): self
   {
      $filter = (new self());
      $parts = explode('.', $propertyName);

      return $filter
         ->setFilterFqcn(__CLASS__)
         ->setAlias($parts[0])
         ->setProperty(str_replace('.','_',$propertyName))
         ->setLabel($label)
         ->setFormType(TextFilterType::class)
         ->setFormTypeOption('translation_domain', 'EasyAdminBundle');
   }

   public function setAlias($alias)
   {
      $this->alias = $alias;
      return $this;
   }

   public function apply(QueryBuilder $queryBuilder, FilterDataDto $filterDataDto, ?FieldDto $fieldDto, EntityDto $entityDto): void
   {
      $property = str_replace($this->alias.'_', '', $filterDataDto->getProperty());
      $comparison = $filterDataDto->getComparison();
      $parameterName = $filterDataDto->getParameterName();
      $countries = $filterDataDto->getValue();

      $em = $queryBuilder->getEntityManager();
      $meta = $em->getClassMetadata($entityDto->getFqcn());
      $mappingInfo = $meta->getAssociationMapping($this->alias);

      $queryBuilder
         ->innerJoin($mappingInfo['targetEntity'], $this->alias, Expr\Join::WITH, 'entity.'. $this->alias.' = '. $this->alias.'')
         ->andWhere(sprintf('%s.%s %s :%s', $this->alias, $property, $comparison, $parameterName))
         ->setParameter($parameterName, $countries);
   }
}

Example usage:

// text field example
$filters->add(AssociationFilter::new('user.company_name', $this->translator->trans('company_name')));

// choice field example
$filters->add(AssociationFilter::new('dealer.locale', $this->translator->trans('market_locale_user'))
            ->setFormType(ChoiceFilterType::class)
            ->setFormTypeOption('value_type_options.choices', $this->localeService->getLocaleChoices())
         )

[Yopai]

I made something like AssociationFilter, too. But you have to replace dots with _, then handle underscore ...

I suggest an EasyAdmin evolution, doing the following : ** Separate form name and property name, cause it has no reason to be always the same

• in FilterTrait and FilterDto, add a "setName" method, to get separate name of filter, and property name. by compatibility, setProperty would set the name if it is not already set.
• when dealing with the form, use the name of the dto; when building the query, use the property name

** automatically handle "a.b" properties

• in all FilterInterface classes (maybe through the FilterTrait), when getting entityAlias and propertyName in the querybuilder creation, handle joining the good table (if it's not already)

** In fact, the way of getting the query builder for a given property should itself be in a separate method. Even maybe settable by a callable ? So a filter (which is definig the form used to filter) can act on any virtual property - however it is set in the query builder

What do you think ? I can work on it, then push a PR for that, but I preferred having your opinion before working on it.

I also would update the documentation (this would even be a good starting point of dicsussion)

[cephalopodius]

This saved my day! Thank a lot. What should i add to be able to choose multiple choice ?

[tsikora666]

How I would handle it:

• Create a custom filter src/Filter/CountryFilter.php

<?php

namespace App\Filter;

use App\Entity\Country;
use App\Entity\Customer;
use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\Query\Expr;
use Doctrine\ORM\QueryBuilder;
use EasyCorp\Bundle\EasyAdminBundle\Dto\EntityDto;
use EasyCorp\Bundle\EasyAdminBundle\Dto\FieldDto;
use EasyCorp\Bundle\EasyAdminBundle\Dto\FilterDataDto;
use EasyCorp\Bundle\EasyAdminBundle\Filter\FilterTrait;
use EasyCorp\Bundle\EasyAdminBundle\Form\Filter\Type\EntityFilterType;

class CountryFilter
{
    use FilterTrait;

    public static function new(string $propertyName, $label = null): self
    {
        return (new self())
            ->setFilterFqcn(__CLASS__)
            ->setProperty($propertyName)
            ->setLabel($label)
            ->setFormType(EntityFilterType::class)
            ->setFormTypeOption('value_type_options', [
                'class' => Country::class,
                'multiple' => true,
                'query_builder' => function (EntityRepository $repository) {
                    return $repository
                        ->createQueryBuilder('country')
                        ->orderBy('country.name', 'ASC');
                }
            ]);
    }

    public function apply(QueryBuilder $queryBuilder, FilterDataDto $filterDataDto, ?FieldDto $fieldDto, EntityDto $entityDto): void
    {
        $alias = 'customer';
        $property = $filterDataDto->getProperty();
        $comparison = $filterDataDto->getComparison();
        $parameterName = $filterDataDto->getParameterName();
        $countries = $filterDataDto->getValue();

        $queryBuilder
            ->innerJoin(Customer::class, $alias, Expr\Join::WITH, 'entity.customer = customer')
            ->andWhere(sprintf('%s.%s %s :%s', $alias, $property, $comparison, $parameterName))
            ->setParameter($parameterName, $countries);
    }
}

• Add this filter in src/Controller/Admin/OrderCrudController.php

<?php

namespace App\Controller\Admin;

use App\Filter\CountryFilter;
use EasyCorp\Bundle\EasyAdminBundle\Config\Filters;
use EasyCorp\Bundle\EasyAdminBundle\Controller\AbstractCrudController;
// ...

class OrderCrudController extends AbstractCrudController
{
    // ...
    public function configureFilters(Filters $filters): Filters
    {
        return $filters->add(CountryFilter::new('country'));
    }
    // ...
}

Is there a way to pass variable from CrudController to

            'query_builder' => function (EntityRepository $repository) {
                return $repository
                    ->createQueryBuilder('country')
                    ->orderBy('country.name', 'ASC');
            }

in a nice way?

[thomas-l]

I am realizing I never fully followed up, my apologies... Thank you @vic-blt! And I did not have time to try the proper way before switching to another project...

[fdiedler]

@javiereguiluz I was confronted to the same issue and the official documentation is wrong https://symfony.com/bundles/EasyAdminBundle/current/filters.html#unmapped-filters

I propose to remove the section with mapped(false) because this function does not exist.

Do you plan to allow filtering for nested properties like users.value or even better users.market.name ?

Thanks,

[phracktale]

@fdiedler I've modified the filter for my proper need for filtering with relations in cascade as defaultTeacherSchool.address.academy.name

namespace App\Admin\Filter;

use Doctrine\ORM\Query\Expr;
use Doctrine\ORM\QueryBuilder;
use EasyCorp\Bundle\EasyAdminBundle\Contracts\Filter\FilterInterface;
use EasyCorp\Bundle\EasyAdminBundle\Dto\EntityDto;
use EasyCorp\Bundle\EasyAdminBundle\Dto\FieldDto;
use EasyCorp\Bundle\EasyAdminBundle\Dto\FilterDataDto;
use EasyCorp\Bundle\EasyAdminBundle\Filter\FilterTrait;
use EasyCorp\Bundle\EasyAdminBundle\Form\Filter\Type\TextFilterType;

class AssociationFilter implements FilterInterface
{

   use FilterTrait;

   protected $tables;
   protected $joinClass;

   public static function new(string $propertyName, $label = null): self
   {
      $filter = (new self());
      $tables = explode('.', $propertyName);

      return $filter
         ->setFilterFqcn(__CLASS__)
         ->setTables($tables)
         ->setProperty(str_replace('.','_',$propertyName))
         ->setLabel($label)
         ->setFormType(TextFilterType::class)
         ->setFormTypeOption('translation_domain', 'EasyAdminBundle');
   }

   public function setTables($tables)
   {
      $this->tables = $tables;
      return $this;
   }

   public function apply(QueryBuilder $queryBuilder, FilterDataDto $filterDataDto, ?FieldDto $fieldDto, EntityDto $entityDto): void
   {
      $comparison = $filterDataDto->getComparison();
      $parameterName = $filterDataDto->getParameterName();
      $parameterValue = $filterDataDto->getValue();

      $em = $queryBuilder->getEntityManager();
      
      $table = "entity";
      for ($count=0; $count<count($this->tables)-1; $count++) {
         $idTable = substr($this->tables[$count], 0, 1) . "_" . $count;
         $queryBuilder->join($table . '.' . $this->tables[$count], $idTable);
         $table = $idTable;
      }
      $property = $this->tables[$count];
 
      $queryBuilder
         ->andWhere(sprintf('%s.%s %s :%s', $table, $property, $comparison, $parameterName))
         ->setParameter($parameterName, $parameterValue);
   }
}

And you can use it with :

return $filters->add(AssociationFilter::new('defaultTeacherSchool.address.academy.name', 'Academy'))

[treztreiz]

By using the AssociationFilter made by @cubaguest, I couldn't create more than one filter per target entity. The reason is that the filters are concatenated in one single query, and by calling the AssociationFilter multiple times, the query will contains all the inner joins made for the same target entity, which lead to an error ('alias' is already defined).

Here is a little improvement to make it work :

public function apply(QueryBuilder $queryBuilder, FilterDataDto $filterDataDto, ?FieldDto $fieldDto, EntityDto $entityDto): void
    {
        $property = str_replace($this->alias.'_', '', $filterDataDto->getProperty());
        $comparison = $filterDataDto->getComparison();
        $parameterName = $filterDataDto->getParameterName();
        $countries = $filterDataDto->getValue();

        $em = $queryBuilder->getEntityManager();
        $meta = $em->getClassMetadata($entityDto->getFqcn());
        $mappingInfo = $meta->getAssociationMapping($this->alias);

        // Retrieve the dql
        $baseQuery = $queryBuilder->getDQL();
        $targetEntity = $mappingInfo['targetEntity'];
        
        // Check if the dql already contains a reference to the target entity
        if (strpos($baseQuery, $targetEntity) === false) {
            $queryBuilder->innerJoin($targetEntity, $this->alias, Expr\Join::WITH, 'entity.'. $this->alias.' = '. $this->alias.'');
        }

        $queryBuilder
            ->andWhere(sprintf('%s.%s %s :%s', $this->alias, $property, $comparison, $parameterName))
            ->setParameter($parameterName, $countries);
    }

[wehostadm]

@javiereguiluz @treztreiz @phracktale Thanks for the Association Field filter improved. I think, it could be added to the last version of EasyAdmin ?

[Mozoou]

By using the AssociationFilter made by @cubaguest, I couldn't create more than one filter per target entity. The reason is that the filters are concatenated in one single query, and by calling the AssociationFilter multiple times, the query will contains all the inner joins made for the same target entity, which lead to an error ('alias' is already defined).

Here is a little improvement to make it work :

public function apply(QueryBuilder $queryBuilder, FilterDataDto $filterDataDto, ?FieldDto $fieldDto, EntityDto $entityDto): void
    {
        $property = str_replace($this->alias.'_', '', $filterDataDto->getProperty());
        $comparison = $filterDataDto->getComparison();
        $parameterName = $filterDataDto->getParameterName();
        $countries = $filterDataDto->getValue();

        $em = $queryBuilder->getEntityManager();
        $meta = $em->getClassMetadata($entityDto->getFqcn());
        $mappingInfo = $meta->getAssociationMapping($this->alias);

        // Retrieve the dql
        $baseQuery = $queryBuilder->getDQL();
        $targetEntity = $mappingInfo['targetEntity'];
        
        // Check if the dql already contains a reference to the target entity
        if (strpos($baseQuery, $targetEntity) === false) {
            $queryBuilder->innerJoin($targetEntity, $this->alias, Expr\Join::WITH, 'entity.'. $this->alias.' = '. $this->alias.'');
        }

        $queryBuilder
            ->andWhere(sprintf('%s.%s %s :%s', $this->alias, $property, $comparison, $parameterName))
            ->setParameter($parameterName, $countries);
    }

just you need to add parentheses (: %s) in the andWhere