Union with Doctrine

Question

I need to create a QueryBuilder with union, is that possible ?

$qb = $this->em->createQueryBuilder()
   ->select('table1.numObject AS id')
   ->select ('table1.name AS name')
   ->from("MyBundle:Table1", "table1")

and union

   ->select('table2.id AS id')
   ->select ('table2.name AS name')
   ->from("MyBundle:Table2", "table2")

Note : it has to be a queryBuilder object (not query or something else)

Thank you

Smashandgrab

Answer 1

Unfortunately UNION is not possible within Doctrine. You have to fetch two sets from database and do "union" manually on php side or use native sql.

Check this issue for more information

Bloomy

Answer 2

It's not possible via the Doctrine syntax, but it is possible using plain SQL and a statement:

    $conn = $this->getEntityManager()->getConnection();

    $sql = <<<SQL
SELECT numObject AS id, name FROM table1

UNION // or UNION ALL if you want duplicates

SELECT id, name FROM table2
SQL;

    try {
        $stmt = $conn->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAll();
    } catch (DBALException $e) {
        return [];
    }

This will give you either an array with the rows you requested or an empty array in case of an error.

Surface

do you have links to any documentation regarding the <<<SQL syntax? – Tirado

@JoelLau php.net/manual/en/… – Prod

Answer 3

As Tomaz answered above, it's not possible to create a UNION using the createQueryBuilder function but we can achieve it by using createNativeQuery. Please check the below example,

public function firstQuery(array $filters) {
    $qb = $this->em->createQueryBuilder()
            ->select('table1.numObject AS id, table1.name AS name')
            ->from("MyBundle:Table1", "table1");

    return $qb->getQuery();
}

public function secondQuery(array $filters) {
    $qb = $this->em->createQueryBuilder()
            ->select('table2.numObject AS id, table2.name AS name')
            ->from("MyBundle:Table2", "table2");

    return $qb->getQuery();
}

// Main function
public function getAPIResult(array $filters, int $pageSize, int $page) {
    $rsm = new ResultSetMappingBuilder($this->em);

    $rsm->addScalarResult('id', 'id', 'integer');
    $rsm->addScalarResult('id', 'name', 'string');

    $sql = "SELECT * FROM (
         ({$this->firstQuery($filters)->getSQL()})
         UNION ALL
         ({$this->secondQuery($filters)->getSQL()})
     ) tmp 
    ORDER BY name
    LIMIT ? OFFSET ?
    ";
    $query = $this->em->createNativeQuery($sql, $rsm);

    $this->em->setParameter(1, $pageSize)
       ->setParameter(2, $pageSize * ($page-1));

    return $query->getScalarResult();
}

Check official docs for Scalar Result: https://www.doctrine-project.org/projects/doctrine-orm/en/2.15/reference/native-sql.html#scalar-results

Note: The above code snippet is not tested, please put a comment if face any issues.

Hope it helps. Happy coding ;)

Kohlrabi

This solution will not work if your queries have parameters, because getSQL method returns prepared query with ? placeholders instead parameters (even if you use named ones) and mapping logic is in Query::execute method – Winery

Yes @BoShurik, You're correct but it was just an example if you have one or more parameters inside the firstQuery or secondQuery, you would need to set the parameters after creating the native query using the createNativeQuery but with the correct parameter index numbers. – Kohlrabi