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

in symfony project, have 2 entites product.php , configuration.php.

it's manytomany relation.

this 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 configuration.php :

/**  * describes products using $this configuration  *  * @var \doctrine\common\collections\collection  *  * @orm\manytomany(targetentity="product", mappedby="configurations")  **/ protected $products; 

in page, need recover products wich do not have configurations, e-g fk configurations null.

so create querybuilder()/dql in productrepository.php :

public function getproductforcurrentworktype($slug)   {     // first product 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));      // apply filter configurations null ptoduct.     // products product.id  not in ids of 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 if return first query e-g $selectproduct. retuns me on product wich have configuration $slug. if return second query (the code below), error occured:

2/2 [syntax error] line 0, col 69: error: expected doctrine\orm\query\lexer::t_from, got ','

1/2 queryexception: select pr mybundle:product pr pr.id not in(select p, wt, c mybundle:product p inner join p.worktype wt wt.id = p.worktype inner join p.configurations c wt.slug = :slug)

how can fix error ?

edit

another solution tried, in 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 collection empty       ->where('wt.slug = :slug')       ->setparameters(array('slug' => $slug));        return $selectproduct             ->getquery()             ->getresult(); 

and controller if :

$em = $this->getdoctrine()->getmanager(); $arrayproduct = $this->getdoctrine()->getrepository('mybundle:product')->getproductforcurrentworktype($slug);  if (!$arrayproduct) {     throw $this->createnotfoundexception('unable find product !'); }  //return statement 

this code below returns me unable find product !, if remove if condition throw $this->createnotfoundexception, page displaying right no results.

try 1 :

$qb->select('p')    ->from('product','p')    ->leftjoin('p.configurations','c')    ->having('count(c.id) = 0') // check if collection empty    ->groupby('p.id');