Reusing QueryBuilder in Doctrine DBAL

Question

The following example shows some extract of an code example. Invoking the QueryBuilder of Doctrine DBAL is done twice there - once for executing a SELECT(*) statement and prior to that executing a COUNT(*) statement.

Common settings like table, conditions, sorting order and result limits are applied to the reused QueryBuilder object.

Questions

• Are there drawbacks of implicitly reusing $queryBuilder like shown in the example?
• Is it suggested to just copy-paste the code for separate QueryBuilder instances?
• Are the side-effects in using clone $queryBuilder?

Code Example

/**
 * @param array $arguments
 * @return string
 */
private function getOutput(array $arguments)
{
    /** @var \Doctrine\DBAL\Connection $connection */
    $connection = $this->getConnection();

    $queryBuilder = $connection
        ->createQueryBuilder()
        ->from('some_table')
        ->orderBy('sorting')
        ->setMaxResults(100);

    $condition = $queryBuilder->expr()->andX();
    // ... build conditions
    $queryBuilder->where($condition);

    $count = $queryBuilder->select('COUNT(*)')->execute()->fetchColumn(0);
    if ($count === 0) {
        return 'There is nothing to show';
    }
    if ($count > 100) {
        $output = 'Showing first 100 results only:' . PHP_EOL;
    } else {
        $output = 'Showing all results:' . PHP_EOL;
    }

    // implicitly reusing previously defined settings
    // (table, where, orderBy & maxResults)
    $statement = $queryBuilder->select('*')->execute();
    foreach ($statement as $item) {
        $output .= $this->renderItem($item) . PHP_EOL;
    }

    return $output;
}

Answer 1

Update Oct 2023

from https://www.derhansen.de/2023/10/the-pitfalls-of-reusing-typo3-querybuilder-analyzing-a-performance-bottleneck.html

TL;DR: Do never reuse an instance of the TYPO3 QueryBuilder for queries, even if the query is the same but with different parameters, since this causes a significant performance decreasement when processing larger amount of records.

---

Original Answer

The QueryBuilder in Doctrine DBAL can be used dynamically to define SQL queries and also to override query parts again. Thus, in general invoking the select() method twice on the same QueryBuilder instance overrides the previous select query part. The builder internally has a property for a clean or dirty state - once the state is dirty, the SQL string has to be recreated. Overriding query parts for instance triggers the dirty state.

So, in general reusing the QueryBuilder is possible and fine from a plain technical view. However, the QueryBuilder does not validate cross-database specific logic. This means, that superfluous query parts have to be purged manually to avoid query failures when executing the statement.

A better approach is to separate the process of building a query into different logical class methods - one to assign the common query constraints and others for specific contexts (e.g. counting results vs. ordering and limiting the result set). In the end, the initial code example might look like this:

Two additional methods are introduced to enrich the query

/**
 * @param QueryBuilder $queryBuilder
 */
private function addConstraints(QueryBuilder $queryBuilder)
{
    $condition = $queryBuilder->expr()->andX();
    // ... build conditions
    $queryBuilder->where($condition);
}

/**
 * @param QueryBuilder $queryBuilder
 */
private function addResultSettings(QueryBuilder $queryBuilder)
{
    $queryBuilder
        ->orderBy('sorting')
        ->setMaxResults(100);
}

Now there are two instances of the QueryBuilder, the query is however basically defined in the previously shown two new methods.

/**
 * @param array $arguments
 * @return string
 */
private function getOutput(array $arguments)
{
    /** @var \Doctrine\DBAL\Connection $connection */
    $connection = $this->getConnection();

    // first query builder instance for counting records
    $queryBuilder = $connection->createQueryBuilder()->from('some_table');
    $this->addConstraints($queryBuilder);
    $statement = $queryBuilder->select('COUNT(*)')->execute();

    $count = $statement->fetchColumn(0);
    if ($count === 0) {
        return 'There is nothing to show';
    }
    if ($count > 100) {
        $output = 'Showing first 100 results only:' . PHP_EOL;
    } else {
        $output = 'Showing all results:' . PHP_EOL;
    }

    // second query builder instance to actually retrieve result set
    $queryBuilder = $connection->createQueryBuilder()->from('some_table');
    $this->addConstraints($queryBuilder);
    $this->addResultSettings($queryBuilder);
    $statement = $queryBuilder->select('*')->execute();

    foreach ($statement as $item) {
        $output .= $this->renderItem($item) . PHP_EOL;
    }

    return $output;
}