Query Exception when select on entity where ForeignKey is NULL in a ManyToMany Doctrine queryBuilder()

Question

In my Symfony project, I have two entities Product.php and Configuration.php.

It's a ManyToMany relation.

This is relation in Product.php:

/**
 * @var \Doctrine\Common\Collections\Collection
 *
 * @ORM\ManyToMany(targetEntity="Configuration", inversedBy="products")
 * @ORM\JoinTable(name="product_configuration",
 *     joinColumns={@ORM\JoinColumn(name="product_id", referencedColumnName="product_id", nullable=true)},
 *     inverseJoinColumns={@ORM\JoinColumn(name="configuration_id", referencedColumnName="configuration_id")}
 * )
 **/
protected $configurations;

and this is Configuration.php:

/**
 * Describes the products using $this configuration
 *
 * @var \Doctrine\Common\Collections\Collection
 *
 * @ORM\ManyToMany(targetEntity="Product", mappedBy="configurations")
 **/
protected $products;

In a page, I need to recover products which do not have some configurations, e-g where FK configurations is NULL.

So I create this queryBuilder()/dql in Productrepository.php :

public function getProductForCurrentWorkType($slug)
  {
    // First we get all the product even with configurations
    $selectProduct = $this->getEntityManager()->createQueryBuilder();

    $selectProduct
      ->select('p, wt, c')
      ->from('MyBundle:Product', 'p')
      ->join('p.workType', 'wt', 'WITH', 'wt.id = p.workType')
      ->join('p.configurations', 'c')
      ->where('wt.slug = :slug')
      ->setParameters(array('slug' => $slug));

    // Then we apply a filter where configurations is null for product.
    // We get all products where product.id  are not in ids of the first request
    $nullConfiguration = $this->getEntityManager()->createQueryBuilder();

    $nullConfiguration
      ->select('pr.id')
      ->from('MyBundle:Product', 'pr')
      ->where($nullConfiguration->expr()->notIn('pr.id', $selectProduct->getDQL()));

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

The fist Step works well if I return only the first query e-g $selectProduct. It returns on only the product which have a configuration for $slug. But if I return the second query (the code below), an error occurred:

2/2 [Syntax Error] line 0, col 69: Error: Expected Doctrine\ORM\Query\Lexer::T_FROM, got ','

1/2 QueryException: SELECT pr FROM MyBundle:Product pr WHERE pr.id NOT IN(SELECT p, wt, c FROM MyBundle:Product p INNER JOIN p.workType wt WITH wt.id = p.workType INNER JOIN p.configurations c WHERE wt.slug = :slug)

How can I fix this error?

EDIT

Another solution I tried, in my ProductRepository.php :

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

    $selectProduct
      ->select('p, wt, c')
      ->from('MyBundle:Product', 'p')
      ->join('p.workType', 'wt', 'WITH', 'wt.id = p.workType')
      ->join('p.configurations', 'c')
      ->having('COUNT(c.id) = 0') // check if the collection is empty
      ->where('wt.slug = :slug')
      ->setParameters(array('slug' => $slug));

      return $selectProduct
            ->getQuery()
            ->getResult();

And now my controller if it could help you:

$em = $this->getDoctrine()->getManager();
$arrayProduct = $this->getDoctrine()->getRepository('MyBundle:Product')->getProductForCurrentWorkType($slug);

if (!$arrayProduct) {
    throw $this->createNotFoundException('Unable to find a Product !');
}

//return statement

This code below returns me Unable to find a Product !, If I remove the if condition with the throw $this->createNotFoundException, the page is displaying right but no results.

Answer 1

Try with this one :

$qb->select('p')
   ->from('Product','p')
   ->leftJoin('p.configurations','c')
   ->having('COUNT(c.id) = 0') // check if the collection is empty
   ->groupBy('p.id');