Using choice field to filter aggregated field in manyToMany list

[raymon2683]

Hello guys,

I would like to move a filter to aggregations for obvious reasons but I'm struggling to implement the filters on them. I'm not sure if what I'm facing is a missing implementation or me getting this completely wrong.
The scenario is that i've a manyToMany relationship between 2 entities Parents and Training (Owning side).I'm displaying a list of Parents details including the number of trainings they are linked to using a count() in the repository query.
Using the lexikFilterBundle i've been able to implement filter based on the various properties of each entities. However, i'm unable to apply a filter (Radio button check to select only parents linked to at least one training or linked to more than one) on this calculated column since it doesn't belong to any of the entities.

public function buildForm(FormBuilderInterface $builder, array $options)
    {
        $builder->add('Firstname', 'filter_text', array('condition_pattern' => FilterOperands::STRING_BOTH,))
                ->add('Lastname', 'filter_text', array('condition_pattern' => FilterOperands::STRING_BOTH,))
                ->add('trainings','filter_choice',array('choices'=> array('0'=>'link to at least 1 training','1'=>'Linked to more than one Training'),
                                                            'expanded'=>true, 'label'=>'Nombre de formations',
                                                            'apply_filter'=> function(QueryInterface $filterQuery,$field,$values){
                                                                if(empty($values['values'])){
                                                                    return null; }
                                                                $paramName = sprintf('p_%s',str_replace('.',',',$field));
                                                                //expression that represent the condition
                                                                $qb = $filterQuery->getQueryBuilder();
                                                                $expression = $qb->having($qb->expr()->gte($qb->expr()->count('t.id'), ':'.$paramName));

                                                                $parameters = array($paramName => $values['value'][0]);

                                                                return $filterQuery->createCondition($expression,$parameters);
                                                                },
                ))

The repository :

public function getParentsDetails()
    {   $qb = $this->createQueryBuilder('p')
                ->select('p.id', 'p.Firstname', 'p.Lastname','p.DOB', 'p.Email') 
                ->addSelect('count(t.id) as trainings')
                ->leftJoin('p.trainings','t')
                ->groupBy('p.id')
                ->orderBy('trainings','DESC');
        return $qb;
    } 

It's however throws the following error which i don't get since im not adding any item.

ContextErrorException: Catchable Fatal Error: Argument 1 passed to tuto\LexikTestBundle\Entity\Parents::addTraining() must be an instance of tuto\LexikTestBundle\Entity\Training, integer given, called in
While im no trying to add anything.

[cedric-g]

Hi, I think you can add the 'mapped' => false option on your trainings field, it should avoid the exception.

[raymon2683]

Thanks , will try that .

[raymon2683]

@cedric-g sorry for the delay. I did try the 'mapped'=>false but didn't help. I this time rather get an Expression of type 'Doctrine\ORM\QueryBuilder' not allowed in this context error being thrown.

From what i could gather , i seems that it can't either create the HAVING expression and attach it to the query after the group by.

[qRoC]

Sample manyToMany:

'apply_filter' => function(QueryInterface $filterQuery, $field, $values) {

                $ids = array_map(function($v) { return $v->getId(); }, $values['value']->toArray());
                $count_ids = count($ids);

                if ($count_ids) {
                    $query = $filterQuery->getQueryBuilder();
                    $query->having('COUNT(DISTINCT m.id) = ' . $count_ids);
                    $query->groupBy('e.id');
                    $query->leftJoin($field, 'm',  \Doctrine\ORM\Query\Expr\Join::WITH, $query->expr()->in('m.id', $ids));
                }
            }

[raymon2683]

Thanks @qRoC ; Im trying to get it working with the fact that Doctrine has issues with'Having' clause .
So i need to rewrite the query to fit with the Ouptwalkers for the KNP paginator