DQL Perform a union in query

Question

I know there are several post relatives to this question but I still can't do mine !

I have two tables (studios and models). I want to perform an union for my datatable.

Currently, I have this :

$cnn = $this->Doctrine->getConnection(); 


    $inscr = $cnn->fetchAll("(SELECT s.thedate AS mydate, s.name AS designation, \'Studio\' AS mytype
             FROM studios s ORDER BY mydate LIMIT 2)
                UNION
            (SELECT m.thedate AS mydate, m.nickname AS designation, \'Hotesse\' AS mytype
             FROM  models m ORDER BY mydate LIMIT 2
            )");


    return $inscr;

But nothing appears in my datatable and I can't perform any var_dump or other.

I test this in my RDBMS and I get waited results. So, anyone could help ?

Answer 1

I finally found a solution to perform the query and get a result

  $sql = "(SELECT s.thedate AS mydate, s.name AS designation, 'Studio' AS mytype
 FROM studios s ORDER BY mydate LIMIT 2)
    UNION
(SELECT m.thedate AS mydate, m.nickname AS designation, 'Hotesse' AS mytype
 FROM  models m ORDER BY mydate LIMIT 2
)";


    $stmt = $this->em->getConnection()->prepare($sql);
    $stmt->execute();
    return $stmt->fetchAll();

But, unfortunatly in datatable the result must be an instance of Doctrine\ORM\QueryBuilder and my solution returns an array..

If someone has an idea for my special case, I'm here !