user3911183 February 2016

How to implement intersect in doctrine dql

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.

Answers


user3911183 February 2016

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.

Post Status

Asked in February 2016
Viewed 2,911 times
Voted 4
Answered 1 times

Search




Leave an answer