0

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!

Tom
  • 1,547
  • 7
  • 27
  • 50

1 Answers1

0

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

M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118
  • Is there an option that the total_options value is not returnt? So that is counter value is used only inside the query? – Tom May 28 '21 at 15:11
  • @Tom you can use [`HIDDEN`](https://www.doctrine-project.org/projects/doctrine-orm/en/2.8/reference/dql-doctrine-query-language.html#select-expressions) keyword in query builder for a column to hide from result set as `COUNT(DISTINCT opt.id) AS HIDDEN total_options` – M Khalid Junaid May 29 '21 at 14:48
  • Would it be possible to add extra conditions to the ->andWhere($query->expr()->in('opt.id', ':optionIds'))? Ik like to add not only to match the opt.id but also a min and max value on an other field in the same entity. – Tom Jun 07 '21 at 07:03
  • @Tom It would be good if you can ask a new question with all the relevant details in order to understand for other users – M Khalid Junaid Jun 07 '21 at 08:48