symfony - filter manytomany with multiple checkboxes

Question

I want to filter results based on manytomany relation with multiple checkboxes.

I have 2 entities. 1) "properties" 2) "views". Where in properties a view arraycollection is being added with a manytomany field based on view entity.

Now I made a multiplecheckbox form based on what values views has (i made it manuel for now). This shows up in the form. However i don't know how to filter the results based on the checkboxes.

As hmtl formoutput i get the following multiple input fields with an array element

<input type="checkbox" name="form[viewchoices][]" value=1">
<input type="checkbox" name="form[viewchoices][]" value=2">
<input type="checkbox" name="form[viewchoices][]" value=2">

I now try to filter in the controller with query. First get the data

    $views = array();
    $views["viewchoices"] = $form["viewchoices"]->getData();

and then i try to do the filter

 if ($views["viewchoices"]) {
            $pagination->innerJoin('a.propviews', 'd')
                    ->addSelect('d');

            foreach ($views['viewchoices'] as $view)
            {
            $pagination->andWhere('d.id like :view')
             ->setParameter('view', $view);
            }

I render the output with knp paginator. When i try to filter i get an error 'Too few parameters: the query defines 5 parameters but you only bound 4'. But i don't think my code is optimal. Any ideas how to do this? Thanks in advance!

updated code: I have multiple filter fields. With the multiplecheckboxes other fields get ignored.

        $pagination = $propertyRepo->createQueryBuilder('a');
           $pagination->Where('a.draft = 1')


       if($location != NULL) {
        $pagination->innerJoin('a.proplocation', 'b')
            ->addSelect('b')
            ->andWhere('b.id like :location')
                ->setParameter('location', $location);
        }

       if($type != NULL) {
        $pagination->innerJoin('a.proptype', 'c')
        ->addSelect('c')
            ->andWhere('c.id like :type')
                ->setParameter('type', $type);
        }

 if ($views["viewchoices"]) {
            $pagination->innerJoin('a.propviews', 'd')
                    ->addSelect('d');

            foreach ($views['viewchoices'] as $view['viewchoices'])
            {
            $pagination->orWhere('d.id =' .(int)$view['viewchoices']);
            // ->setParameter('view', $view);
            }
}


 if ($rooms["roomchoices"]) {
            $pagination->innerJoin('a.proprooms', 'e')
                    ->addSelect('e');

            foreach ($rooms['roomchoices'] as $room['roomchoices'])
            {
            $pagination->orWhere('e.id =' .(int)$room['roomchoices']);
            // ->setParameter('view', $view);
            }
}
        $pagination->getQuery();

The part in the foreach-loop looks weird. Your query has a parameter `:viewchoices`, but you set a parameter `id` instead. Also your comment metions orWhere, but you use `andWhere`. Could you maybe add an example how the generated query should look like? — dbrumann, May 02 '19 at 05:07
@dbrumann oh haha that comment was part of a copypast thing (and andwhere should be used as it's a multiple thing). I updated my code a bit, i don't get the error but it also doesn't filter. Yeah, i don't know where to find a good example. I am not so familair with the foreachloop. — Missblues, May 02 '19 at 07:32
Are you sure you want `andWhere`? This will create something like `WHERE d.id LIKE 1 AND d.id LIKE 2 AND d.id LIKE 3...` This does not make much sense to me. I think you actually want `OR` here or, alternatively, something like `... AND d.id IN (1,2,3,...)` (which is just a fancy way for `id = 1OR id = 2 OR...`). Please add an example SQL-query that you think would work or more details on the model & pagination process, so we get a more complete picture why the query won't return any results. — dbrumann, May 02 '19 at 08:14
@dbrumann haha with orwhere i do get a list back :). but i have more filter options (dropdown/slider etc) and it seems now that with the orwhere option the other filters get ignored. Let me update the code to show you what i have. i took the following as example btw https://github.com/lexik/LexikFormFilterBundle/issues/40 — Missblues, May 02 '19 at 11:47
@dbrumann now i get the multiple checkboxes to be shown manually ( i just add the choice options in de formbuilder) but i want actually to grab it from a collectionarray. However i don't know how to get that. i posted a quesion about that also. and you can also see more of my code. Maybe you know how to grab the collectionarray. Thanks so far. https://stackoverflow.com/questions/55864386/render-a-filtered-checkbox-collection-in-a-form/55865029?noredirect=1#comment98428272_55865029 — Missblues, May 02 '19 at 12:00
I think i have to implement somthing like mentioned in the last post. Bind all the andors together as an andwhere. https://stackoverflow.com/questions/9095113/how-to-use-andwhere-and-orwhere-in-doctrine — Missblues, May 02 '19 at 14:12

score 1 · Answer 1 · answered May 02 '19 at 15:05

I think i got it working now. I loop trough the orwhere statements and bind it together in an andwhere statement as i also have other andwhere filters.

 if ($facilities["facilities"]) {


            $orStatements = $pagination->expr()->orX();

            $pagination->innerJoin('a.propfacilities', 'f')
                    ->addSelect('f');

            foreach ($facilities['facilities'] as $facility['facilities'])
            {
            $orStatements->add($pagination->expr()->in('f.id', (int)$facility['facilities']));

            }

            $pagination->andWhere($orStatements);
}

I think you can remove the foreach if you do something like: `$pagination->andWhere($pagination->expr()->in('f.id', implode(',', $facilities['facilities'])));`. — dbrumann, May 02 '19 at 15:48

symfony - filter manytomany with multiple checkboxes

1 Answers1