QueryBuilder: Add comments to SQL queries

[jkufner]

Feature Request

Q	A
New Feature	yes
RFC	maybe
BC Break	no

Summary

I would like to add a small comment to every SQL query that says in which method is the query located.

Example:

return $conn->createQueryBuilder()
    ->select('*')
    ->from('some_table')
    ->andWhere('t > NOW()')
    ->setMaxResults(10)
    ->comment(__METHOD__)  //  <-- Add the comment
    ->execute()
    ->fetchAll();

Such code would generate the following query:

SELECT * FROM some_table WHERE t > NOW() LIMIT 10
-- App\SomeRepository::findLatest

Then, in the list of executed queries, we can see which repository executed which query.

It may be useful to have two methods, e.g., commentHeader and commentFooter, that add comments to the begin and the end of the query, respectively.

I used this approach with other query builder and I found it very useful when debugging. It really helps to locate relevant queries.

[greg0ire]

Are you aware of doctrine/DoctrineBundle#954 ? Do you think part of it should be backported to the DBAL?

[jkufner]

@greg0ire Thank you, I was not aware of that.

doctrine:
    dbal:
        profiling_collect_backtrace: true

This option enables that feature and it is very helpful.

However, option to add a comment to a SQL query would allow us to add not only from where the query was executed, it would also allow us to add a note about some additional details, like a reason why it was invoked.

Another reason for the comment is to see from where the query come in the slow log on a production database server, where we do not have the option to show a backtrace, only the query itself.

[emullaraj]

@jkufner this can be probably achieved currently with a combination of setHint and by defining a custom SqlWalker.

<?php

declare(strict_types=1);

namespace Acme\Doctrine\Query;

use Doctrine\ORM\Query\AST\DeleteStatement;
use Doctrine\ORM\Query\AST\SelectStatement;
use Doctrine\ORM\Query\AST\UpdateStatement;
use Doctrine\ORM\Query\SqlWalker;

class CommentSqlWalker extends SqlWalker
{
    public function walkSelectStatement(SelectStatement $AST): string
    {
        return $this->getQueryWithCalleeComment(parent::walkSelectStatement($AST));
    }

    public function walkUpdateStatement(UpdateStatement $AST): string
    {
        return $this->getQueryWithCalleeComment(parent::walkUpdateStatement($AST));
    }

    public function walkDeleteStatement(DeleteStatement $AST): string
    {
        return $this->getQueryWithCalleeComment(parent::walkDeleteStatement($AST));
    }

    protected function getQueryWithCalleeComment(string $query): string
    {
        $traceData = \debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS, 15);

        foreach ($traceData as $traceDatum) {
            // Simplified example of callee from a repository method
            if (\stripos($traceDatum['class'], 'repository') === false) {
                continue;
            }

            return '-- '.$this->getCalleeFromData($traceDatum).PHP_EOL.$query;
        }

        return $query;
    }

    private function getCalleeFromData(array $calleeData): string
    {
        $method = $calleeData['class'] ?? $calleeData['file'];

        if (isset($calleeData['function'])) {
            $method .= '::'.$calleeData['function'];
        }

        if (isset($calleeData['line'])) {
            $method .= '::'.$calleeData['line'];
        }

        return $method;
    }
}

In your Query Builder

$qb->getQuery()->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, CommentSqlWalker::class)

The resulting query would be similar to

-- Acme\Repository\FooRepository::countActive::123
SELECT COUNT(c0_.id) AS sclr_1 FROM foo c0_ WHERE c0_.active = 1

PROS
No need for a new feature

CONS
It doesn't scale and doesn't leverage extension

Event though I think that your proposal would be of great help, especially with the slow log on a production database server.
Maybe introducing a new 'comment' query part together with an event system for the QueryBuilder getSQL/getSQLFor*** can help this feature scale better.