CODEWITHSUNDEEP
symfonydoctrineunion
mlwacosmos
mlwacosmos

Reputation: 4561

Union with Doctrine

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

Upvotes: 5

Views: 11532

Answers (3)

Silambarasan R
Silambarasan R

Reputation: 1556

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 ;)

Upvotes: 1

DerpyNerd
DerpyNerd

Reputation: 4803

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.

Upvotes: 1

Tomasz Madeyski
Tomasz Madeyski

Reputation: 10900

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

Upvotes: 8

Related Questions