How to use DBAL query builder to build this subquery?

Question

I have this query with subquery.

SELECT * FROM 
(SELECT module_id FROM an_modules AS m LIMIT 20 OFFSET 0) AS m 
LEFT JOIN an_module_sites AS ms ON (m.module_id = ms.module_id)

How to use DBAL to build subquery like this?
This doesn't seem to be work.

$qb->select('*')
    ->from(
        $qb->select('module_id')
            ->from($this->Db->getTableName('modules'), 'm')
        , 'm')
    ->leftJoin('m', $this->Db->getTableName('module_sites'), 'ms', 'm.module_id = ms.module_id');
$stmt = $qb->execute();
$result = $stmt->fetchAll();

The query builder doesnt' support a from statement from a query. I suggest you to execute directly via connection `$conn->executeQuery(..` or if you need to interact with parameters deal with a `statement` as `$stmt = $conn->prepare($sql);` then you can bind value etc... — Matteo, Nov 23 '15 at 11:42

score 2 · Answer 1 · answered Dec 18 '15 at 22:09

I recently needed to do this to implement a pagination/sorting helper. As part of this I would take a querybuilder executed by my model and and generically count the total rows it would produce if unlimited.

To be cross platform I couldn't use rowCount and potential grouping meant I couldn't just change the fields in the select - so the best option was to remove the limits and count it as a subquery. This is what I came up with:

<?php
$totalResults = $qb->getConnection()->createQueryBuilder()
    ->select('COUNT(*)')
    ->from(
        '('
        .$qb
            ->setFirstResult(null)
            ->setMaxResults(null)
            ->resetQueryPart('orderBy')
            ->getSQL()
        .')',
        'tmp')
    ->execute()
    ->fetch(\PDO::FETCH_COLUMN);

I'm not sure how doctrine ORM handles this, but in pure DBAL at least this seems to work.

How to use DBAL query builder to build this subquery?

1 Answers1