Sonata Admin custom query in configureListFields

Question

I am stuck on this for several hours.

I have admin class to list all categories and in one table column there are related products (Product entity): Table example Related code:

protected function configureListFields(ListMapper $listMapper)
{
    $listMapper
            ->addIdentifier('name')
            ->add('products') // Entity Product, @ORM\OneToMany
            ->add('ord')
    ;
}

What I need to do is hide inactive products from being listed based on "(boolean) product.active" but I can't figure it out. I know about "createQuery" method but it doesn't work. When I generate SQL and run the query directly it works but here it looks like I can use ProxyQuery only to filter Category and then all Products are queried in separate query (and this separate query I am not sure how to change).

public function createQuery($context = 'list')
{
    $query = parent::createQuery($context);

    $q = new ProxyQuery($query->join(sprintf('%s.products', $query->getRootAlias()), 'p')
            ->andWhere('p.active = :act')->setParameter('act', true));

    return $q;
}

Thank you for any help

score 1 · Answer 1 · edited May 23 '17 at 10:30

1

You can change the type of your field to null or entity and add a query_builder option to adapt the query used :

protected function configureListFields(ListMapper $listMapper)
{
    $listMapper
        ->addIdentifier('name')
        ->add('products', null, array(
            'query_builder' => function(EntityRepository $er) {
                 return $er->createQueryBuilder('qb')
                           ->leftjoin('qb.products', 'p')
                           ->where('p.active = :act')
                           ->setParameter('act', true)
             }
     ));
}

I didn't test it with a oneToMany relation.

There are some information in this topic

edited May 23 '17 at 10:30

Community

1
1

answered Oct 31 '15 at 10:43

Alexandre Tranchant

4,426
4
43
70

1

Thanks for your reply but I still can't make it work. It looks like 'query_builder' is somehow not used. I tried put die(); in that function and nothing happen. – David K Nov 02 '15 at 12:33
Be sure to include the whole path to EntityRepository, not only the name:) It worked like a charm for me – Cowwando May 26 '16 at 08:52

Sonata Admin custom query in configureListFields

1 Answers1