How to implement intersect in doctrine dql

Question

I'm looking for a way to implement the intersect function sql into doctrine dql to use it for some queries. Here is the query i would like to intersect their result :

$sql1 = "SELECT distinct o1 FROM  Application\Entity\Object1 o1 INNER JOIN Application\Entity\Object2 o2 WITH o2.fkObject1 = o1.pkObject1  WHERE o1.etat = 'valide' AND o2.pkObject2 = $id ";

$sql2 = "SELECT distinct o1 FROM  Application\Entity\Object1 o1 INNER JOIN Application\Entity\Object2 o2 WITH o2.fkObject1 = o1.pkObject1  WHERE o1.etat = 'attente' AND o2.pkObject2 = $id_2 ";

$sql = "sql1 INTERSECT $sql2 ";

// returns [Syntax Error] line 0, col 556: Error: Expected end of string, got 'INTERSECT'

$query = $this->getEntityManager()->createQuery($sql);
$results = $query->getResult();

Thanks.

user3911183 user3911183 · Accepted Answer · 2016-02-10T09:43:23

the way it worked for me is to use too many inner join like this :

$sql =  "SELECT distinct o1 
         FROM  Application\Entity\Object1 o1 
         INNER JOIN Application\Entity\Object2 o2 WITH o2.fkObject1 = o1.pkObject1 
         INNER JOIN  Application\Entity\Object2 o3 WITH o3.fkObject1 = o1.pkObject1  
         WHERE o1.etat = 'valide' 
             AND o2.pkObject2 = $id 
             AND o1.etat = 'attente' 
             AND o3.pkObject3 = $id_2 ";

Hope it helps.

How to implement intersect in doctrine dql

1 Answers