Querybuilder with array of Id's in a one to many situation

Question

I'm using Symfony 3.4 and its doctrine querybuilder. I have an entity Ad that has different options in a one too many relation. Therefore I'm building a filter.

$optionIds is a array with multiple integers representing the option.id

I have the following filter:

            $query->leftJoin('ad.options', 'opt')
            ->andWhere($query->expr()->in('opt.id', ':optionIds'))
            ->setParameter('optionIds', $optionIds)

Problem with this filter is dat it returns all ads that have one of the options linked. The idee is to get only the ads that have all id's linked. So this filter is a OR instead-off an AND filter?

Second issue is that it return multiple time the same ad if it matches multiple options ids. I don't want to use groupBy to solve this agina.

I also change the code to the following:

         $cnt = 0;
        foreach ($optionIds as $optionId) {
            $query->leftJoin('ad.options', 'opt'.$cnt)
                ->andWhere('opt'.$cnt.'.id = :id'.$cnt)
                ->setParameter('id'.$cnt++, $optionId);
        }

This works but is very slow.

Help is appreciated, i'm stuk already half a day!

Answer 1

To check if all options should exist for the ad you will need to use aggregation and filter on aggregated result

$query->addSelect('COUNT(DISTINCT opt.id) AS total_options')(
      ->leftJoin('ad.options', 'opt')
      ->andWhere($query->expr()->in('opt.id', ':optionIds'))
      ->addGroupBy('ad.id')
      ->having('total_options = '.count($optionIds))
      ->setParameter('optionIds', $optionIds)

For reference and more details see my other answers for similar situation in a many to many relation

Symfony2 - Doctrine2 QueryBuilder WHERE IN ManyToMany field

Sql/Doctrine query to find data with multiple condition with Many to Many associations