AssociationField::setQueryBuilder() does not behave as documented

[victortodoran]

Describe the bug \EasyCorp\Bundle\EasyAdminBundle\Field\AssociationField::setQueryBuilder() does not behave as documented docs.

Firstly the contract only accepts Closures now. Secondly, despite what is documented, the Closure is expected to configure the implicitly passed query builder (infered from here)

So it seems that we have two options:

1. Configure the QueryBuilder that is implictly passed to the closure provided to setQueryBuilder()
2. Configure the query builder like this $someAssociationField->setFormTypeOption('query_builder', $someQueryBuilder);

To Reproduce

• Easy Admin Bundle 4.0.6
• Create an Entity, a Crud Controller and a form with an association field.
• Try to use AssociationField::setQueryBuilder() as documented

(OPTIONAL) Additional context

I can create a PR to update the docs if you want me too, I just need to know which of the options is the desired public contract.

Cheers

[sinansoezen]

Still the same in version v4.3.4 if anyone comes across this issue. Either I need more sleep or the setQueryBuilder() option doesn't work at all, in neither of the three documented ways in the docs. A temporary solution is to set the FormTypeOption for the QueryBuilder manually with the setFormTypeOption() method

yield AssociationField::new('exampleField')
    ->setFormTypeOption('query_builder', function (EntityRepository $entityRepository) {
        return $entityRepository->createQueryBuilder('e')
            ->andWhere('...')
            ->orderBy('e.exampleField', 'ASC');
    });

or within the setFormTypeOptions method in combination with other FormTypeOptions

yield AssociationField::new('exampleField')
    ->setFormTypeOptions([
        'query_builder' => function (EntityRepository $entityRepository) {
            return $entityRepository->createQueryBuilder('e')
                ->andWhere('...')
                ->orderBy('e.exampleField', 'ASC');
        },
        'by_reference' => false,
    ]);

[tonyellow]

Same problem here, setQueryBuilder is not being used when set.

[Wacuta]

I think, I have the same problem.

when I do this, my query is ignored, I have all options on select's form.

yield AssociationField::new('category')->setQueryBuilder(
    fn (QueryBuilder $queryBuilder) => $queryBuilder->getEntityManager()
        ->getRepository(Category::class)
        ->findMainCategories()
);

[jmeyo]

Also bumped into that problem here. I am using a repository method, that works standalone, but when used in new or edit, it does not take it into account to populate the association field entries.

[GeneraleCauchemar]

I was experiencing the same issue as @Wacuta and @jmeyo , on EasyAdmin 4.6.3

This was working:

yield AssociationField::new('parents')
                      ->setQueryBuilder(
                          fn(QueryBuilder $queryBuilder) => $queryBuilder->andWhere('entity.parents IS EMPTY')
                                       ->andWhere('entity != :category')
                                       ->orderBy('entity.title', 'ASC')
                                       ->setParameter('category', $object)
                      )

While this wasn't, even though it should have according to the documentation:

yield AssociationField::new('parents')
                      ->setQueryBuilder(fn(QueryBuilder $queryBuilder) => $queryBuilder->getEntityManager()->getRepository(self::getEntityFqcn())->getFirstLevelCategoriesQb($object))

(getFirstLevelCategoriesQb() does return a Doctrine\ORM\QueryBuilder instance)

I tracked the problem down to the AssociationConfigurator, l.160 to 169. The $queryBuilder parameter wasn't being updated by the $queryBuilderCallable l.166 in my second use case and only defined a SELECT and a FROM, as it does after having just been created by the createQueryBuilder() method.

Long story short: to get it working, I had to pass the $queryBuilder parameter from the callable to my repository method so I would update it rather than create a new instance of a QueryBuilder.

This is doing the trick for me right now but it's not the most intuitive solution: if your repository method declares a new QB instance, it should be the one being used rather than the one defined in the AssociationConfigurator. But I'm not sure what can be done about it, code-wise. If anyone feels up to it...

[luismisanchez]

I was experiencing the same issue as @Wacuta and @jmeyo , on EasyAdmin 4.6.3

This was working:

yield AssociationField::new('parents')
                      ->setQueryBuilder(
                          fn(QueryBuilder $queryBuilder) => $queryBuilder->andWhere('entity.parents IS EMPTY')
                                       ->andWhere('entity != :category')
                                       ->orderBy('entity.title', 'ASC')
                                       ->setParameter('category', $object)
                      )

Same problem here.

@GeneraleCauchemar solution is working for me on Easyadmin 4.6.5 and Symfony 5.4.23.

Thanks for sharing ❤️

[EDITED]

In case someone is looking for a solution to set up a more or less complex filter in an AssociationField, this is my functional code for a category system where you can select a parent category. Therefore, in the parent category selector, the category itself and any of its descendants should not appear:

// src/Entity/DocumentCategory.php

    #[ORM\ManyToOne(targetEntity: self::class, inversedBy: 'children')]
    private ?self $parent = null;

    #[ORM\OneToMany(mappedBy: 'parent', targetEntity: self::class)]
    private Collection $children;

    public function __construct()
    {
        $this->children = new ArrayCollection();
    }

    public function getParent(): ?self
    {
        return $this->parent;
    }

    public function setParent(?self $parent): self
    {
        $this->parent = $parent;

        return $this;
    }

    public function getChildren(): Collection
    {
        return $this->children;
    }

    public function addChild(self $child): self
    {
        if (!$this->children->contains($child)) {
            $this->children->add($child);
            $child->setParent($this);
        }

        return $this;
    }

    public function removeChild(self $child): self
    {
        if ($this->children->removeElement($child)) {
            // set the owning side to null (unless already changed)
            if ($child->getParent() === $this) {
                $child->setParent(null);
            }
        }

        return $this;
    }

// src/Repository/DocumentCategoryRepository.php

    public function getDescendantsIds(int $categoryId): array
    {
        $rsm = new \Doctrine\ORM\Query\ResultSetMapping();
        $rsm->addScalarResult('id', 'id');

        $sql = <<<SQL
                WITH RECURSIVE excluded_categories AS (
                  SELECT id
                  FROM document_category
                  WHERE id = :categoryId
                  UNION ALL
                  SELECT dc.id
                  FROM document_category dc
                  JOIN excluded_categories ec ON dc.parent_id = ec.id
                )
                SELECT id FROM excluded_categories
                SQL;

        $query = $this->_em->createNativeQuery($sql, $rsm);
        $query->setParameter('categoryId', $categoryId);

        return array_column($query->getScalarResult(), 'id');
    }

// src/Controller/Admin/DocumentCategoryCrudController.php

class DocumentCategoryCrudController extends AbstractCrudController
{
    private DocumentCategoryRepository $documentCategoryRepository;
    private RequestStack $requestStack;

    public function __construct(
        DocumentCategoryRepository $documentCategoryRepository,
        RequestStack $requestStack,
    ) {
        $this->documentCategoryRepository = $documentCategoryRepository;
        $this->requestStack = $requestStack;
    }

    public function configureFields(string $pageName): iterable
    {

        $categoryId = $this->requestStack->getCurrentRequest()?->get('entityId');
        $repo = $this->documentCategoryRepository;
        if ($categoryId) {
            $excludedIds = $repo->getDescendantsIds($categoryId);
        }
        $excludedIds[] = $categoryId; // also exclude the category itself

        return [
            AssociationField::new('parent')
                            ->setFormTypeOption('placeholder', 'No parent category')
                            ->setFormTypeOption('required', false)
                            ->setQueryBuilder(
                                fn (QueryBuilder $queryBuilder) => $queryBuilder
                                    ->andWhere($queryBuilder->expr()->notIn('entity.id', ':excludedIds'))
                                    ->setParameter('excludedIds', $excludedIds)
                            ),
                            [...]

[peamak]

Still not working. I'm using the solution provided by @GeneraleCauchemar above, but it should be documented somewhere (at the very least) that the alias to use is "entity".

EDIT: EasyAdmin 4.8, Symfony 7.0.2

[MaBr70]

Still not working. I'm using the solution provided by @GeneraleCauchemar above, but it should be documented somewhere (at the very least) that the alias to use is "entity".

EDIT: EasyAdmin 4.8, Symfony 7.0.2

Hi All, the solution of @GeneraleCauchemar (passing the QueryBuilder to my repo method) is working fine for me (Symfony 6.3.11, EasyAdmin 4.8.9) - Thank you so much!

@peamak: it should be noted, that it is possible to get the underlying alias of the QueryBuilder, using: $rootAlias = $qb->getRootAliases()[0]; This allows you to avoid to hard-code the string 'entity' in your code.

Digging in EasyAdmin code (searching for OPTION_QUERY_BUILDER_CALLABLE - see AssociationConfigurator::configure) I found that the call of setQueryBuilder() has absolutely no effect if the autocomplete() is set as well: in such a case the OPTION_QUERY_BUILDER_CALLABLE is completely ignored. Hope this might help someone.

[MaBr70]

@GeneraleCauchemar I found a trick to have a QueryBuilder created from scratch in the repository method, without having to update the passed one. Don't know if it's really neat, but it works for me...

In the controller, instead of calling setQueryBuilder(fn (QueryBuilder $queryBuilder) => ... it is possible to receive the passed $queryBuilder as reference (see &$queryBuilder below): this way the repo method can create its own one and return it as usual, overwriting thus the passed one.

AssociationField::new('projects')
   //->autocomplete() // As already mentioned, this would make the setQueryBuilder useless...
   ->setQueryBuilder(fn (QueryBuilder &$queryBuilder) =>
   $queryBuilder = $queryBuilder->getEntityManager()->getRepository(Project::class)->getFilteredProjects())