doctrine2 queryBuilder must return only result matching with array values (ids): 0/Null and/or One and/or Many id(s) have to return One result

Question

I have an array of collection named $configurations. This array matches of my Entity Configuration.php connected to Product.php as a ManyToMany. Now I have another entity named WorkType.php which is connected too to Configuration.php by a ManyToMany.

The goal is to recover the product who has O/Null or Many configurations for the current work type.

By default I have a product without configurations, but user could choose by checkboxes a O/Null or One or Many configurations available for a work type.

So I created this queryBuilder in my ProductRepository.php:

public function getProductByManyConfig($slug, $configurations)
  {

    $queryBuilder = $this->getEntityManager()->createQueryBuilder();

    $queryBuilder->select('p')
                 ->from('MyBundle', 'p')
                 ->join('p.workType', 'wt', 'WITH', 'wt.id = p.workType')
                 ->leftJoin('p.configurations','c');

    $queryBuilder->add('where', $queryBuilder->expr()->in('c', ':c'))
                 ->andWhere('wt.slug = :slug')
                 ->setParameters(array('slug'=> $slug, 'c'=> $configurations));

    return $queryBuilder->getQuery()
                        ->getResult();
  }

It works, but the proble is that it returns me all product matching with the array of configurations I pass to the query.

For example if the array have the id 1 and 2 of Configuration.php, the query returns me the products matching with configuration.id = 1, configuration.id = 2 and configuration.id =1 and 2.

So I have 3 conditions to achieve, I need only one; e-g only the product which containing all the configuration.id !

I only need to return the only one product containing the ids of Configuration.php passed into the array for the current WorkType.php !

This is my controller code:

$vars = array(); //containing all values I need for recover configurations properties

$em = $this->getDoctrine()->getManager();

$var = array_values($vars);

$configurations = $this->getDoctrine()->getRepository('MyBundle:Configuration')->findByName($var);

foreach ($configurations as $conf) {
  $name = $conf->getName();
}

$slug = "the_right_database_correspondence";

$arrayProbuctConfig = $this->getDoctrine()->getRepository('MyBundle:Product')->getProductByManyConfig($slug, $configurations);

// return of the view

As the choice of configurations could change, I need to create this dynamic method. How can I return the good result ?

french_dev · Accepted Answer · 2016-01-26 14:21:56Z

5

I had the same problem with Doctrine2 recently and I came with that answer.

If you want all Product entities that are linked with all the configurations, you need to add a where condition for every configuration.

Considering your $configurations variable is an array of Configuration entities

$queryBuilder->select('p')
             ->from('MyBundle', 'p')
             ->join('p.workType', 'wt', 'WITH', 'wt.id = p.workType')
             ->leftJoin('p.configurations','c');

$queryBuilder->where('wt.slug = :slug')->setParameter('slug', $slug);

$nbConfs = count($configurations);
  for($i = 0; $i < count($configurations); $i++){
      $queryBuilder->andWhere(":conf{$i} MEMBER OF p.configurations")->setParameter("conf{$i}", $configurations[$i]);
  }

$queryBuilder
      ->having('COUNT(c.id) =:some_count')
      ->setParameter('some_count', $nbConfs);

I had to use a for loop to create different tokens (the string preceded by :), otherwise Doctrine doesn't add them to its internal collection of parameters.

Edit : Query modified to consider the case where you pass no configurations and want to get Products that has no relations to Configurations.

edited Jan 26, 2016 at 14:21

french_dev

2,17712 gold badges48 silver badges92 bronze badges

answered Jan 25, 2016 at 18:26

Lex Lustor

1,6352 gold badges27 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

french_dev Over a year ago

thank you for your answer, I have not my code with me for now but count on me to try your suggestion. Thank you by the way !

french_dev Over a year ago

your solution works well if I enter 2 or many Configurations, but if my array contain 0/NULL or 1 Configuration the query retuns me all product for the workType. So I need to fix that behavior.

Lex Lustor Over a year ago

@french_dev If I understood well your comment, I edited my code to limit to Product with no configuration when you pass an empty array. Concerning the case when you pass only one configuration, you should only have the Products connected to that Configuration.

french_dev Over a year ago

the same as I told you before, your query works well, and now take care of the condition if the array is empty, but not if the array contain only one parameter. I am searching to create it ! Thank you for your answer.

Lex Lustor Over a year ago

@french_dev Ok now I fully understand what you tried to achieve. Nice collaborative work, thanks to you!

|

Collectives™ on Stack Overflow

doctrine2 queryBuilder must return only result matching with array values (ids): 0/Null and/or One and/or Many id(s) have to return One result

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related