Filtering on many-to-many relationships about lexikformfilterbundle

[aarongeiser]

This helped me a lot. Here is an example of using the 'apply_filters' option with an entity filter to allow a user to select multiple options on a many-to-many relationship from a multi-select box and filter the results.

->add('myEntity', 'filter_entity', array(
    'class'         => 'AppMyBundle:MyEntity',
    'expanded'      => false,
    'multiple'      => true,
    'apply_filter'  => function (QueryInterface $filterQuery, $field, $values)
        {
            $query = $filterQuery->getQueryBuilder();
            $query->leftJoin($field, 'm');

            // Filter results using orWhere matching ID
            foreach ($values['value'] as $value)
            {
                $query->orWhere($query->expr()->in('m.id', $value->getId()));
            }
        },
))

[cedric-g]

By default the filter_entity don't work with many to many and I don't think we will make it able to check the relation type. In this case you will have to use the apply_filter option to change the way the condition is applied on the query (as you saw it in #25).

[rvanlaak]

Still can't get manyToMany relationship filtering working. It seems that this has something to do with the way how Doctrine filters manyToMany relations.

http://stackoverflow.com/a/8959837/1794894

I've been looking in your apply_filter functionality, but can't get it to work. Probably this is because my manyToMany relationship does not have a Doctrine entity class.

 $filterBuilder->leftJoin('BundleName\Language', 'l');
 $filterBuilder->andWhere($expr->inEq('l.id', $values['value']));
 ladybug_dump($filterBuilder->getQuery()->getSQL());

Above code snippet does not filter the results correct, since the link table is not used. Can you please provide a working example on manyToMany filtering?

[cedric-g]

Let's say you have a many to many between User and Language, so in User you should have a languages property. If you want to filter your users by languages, you can do something like:

 $filterBuilder->leftJoin('u.languages', 'l');
 $filterBuilder->andWhere($expr->in('l.id', $values['value'])); // here $values['value'] will be a collection of objects so maybe you will have to transform it into an array of ids to make the `in` expression work correctly.

[rvanlaak]

Ah great, finally got it working! Some important remarks from my side:

1. You don't have to join the entity (in above examle Language), but the relationship itself. What I did is leftJoin Language but this was an incorrect join. This indeed should be u.languages.
2. In manyToMany relationships the filter values indeed need to be transformed in order to, for example make an 'in' expression work. So, $values['value'] contains an ArrayCollection with the selected objects, but the query needs ID's. Loop through the ArrayCollection to manually format the parameter for in the filterBuilder

[djoo]

Hi, thanks for this. This is a version with one value (multiple = false)

$builder->add('myEntity', 'filter_entity', array(
            'class'         => 'AppMyBundle:MyEntity',
            'expanded'      => false,
            'multiple'      => false,
            'apply_filter'  => function (QueryInterface $filterQuery, $field, $values)
            {
                $query = $filterQuery->getQueryBuilder();
                $query->leftJoin($field, 't');
                $value = $values['value'];
                if (isset($value)){
                $query->andWhere($query->expr()->in('t.id', $value->getId()));
                }
            }));

[franciscomemoli]

I did exactly the same but didn't work to me. The problem was that the andWhere don't add the filter at the query, so I did this:

$builder->add('myEntity', 'filter_entity', array(
            'class'         => 'AppMyBundle:MyEntity',
            'expanded'      => false,
            'multiple'      => false,
            'apply_filter'  => function (QueryInterface $filterQuery, $field, $values)
            {
                $query = $filterQuery->getQueryBuilder();
                $query->innerJoin($field, 't');
                $value = $values['value'];
                if (isset($value)){
                    $paramName = sprintf('p_%t', str_replace('.', '_', $field));
                    $expression = $filterQuery->getExpr()->eq("t.id", ':'.$paramName);
                    $parameters = array($paramName => $values['value']->getId()); 
                    return $filterQuery->createCondition($expression, $parameters);
                }
            }));

I changed to innerJoin because I want to filter with and and if you use left join it will no work properly.

[cedric-g]

@franciscomemoli I think you and @djoo are just not using the same version of the bundle. The $filterQuery->createCondition(...) is only available since the version 3.x.