Doctrine - How to print out the real sql, not just the prepared statement?

Question

We're using Doctrine, a PHP ORM. I am creating a query like this:

$q = Doctrine_Query::create()->select('id')->from('MyTable');

and then in the function I'm adding in various where clauses and things as appropriate, like this

$q->where('normalisedname = ? OR name = ?', array($string, $originalString));

Later on, before execute()-ing that query object, I want to print out the raw SQL in order to examine it, and do this:

$q->getSQLQuery();

However that only prints out the prepared statement, not the full query. I want to see what it is sending to the MySQL, but instead it is printing out a prepared statement, including ?'s. Is there some way to see the 'full' query?

Solution 1

Doctrine is not sending a "real SQL query" to the database server : it is actually using prepared statements, which means :

• Sending the statement, for it to be prepared (this is what is returned by $query->getSql())
• And, then, sending the parameters (returned by $query->getParameters())
• and executing the prepared statements

This means there is never a "real" SQL query on the PHP side — so, Doctrine cannot display it.

Solution 2

A working example:

$qb = $this->createQueryBuilder('a');
$query=$qb->getQuery();
// SHOW SQL: 
echo $query->getSQL(); 
// Show Parameters: 
echo $query->getParameters();

Solution 3

You can check the query executed by your app if you log all the queries in mysql:

http://dev.mysql.com/doc/refman/5.1/en/query-log.html

there will be more queries not only the one that you are looking for but you can grep for it.

but usually ->getSql(); works

Edit:

to view all the mysql queries I use

sudo vim /etc/mysql/my.cnf 

and add those 2 lines:

general_log = on
general_log_file = /tmp/mysql.log

and restart mysql

Edit 2 In case you dont find the mysql config (it can be in many places), just set those variables from mysql command line.

mysql -u root -p 

SHOW VARIABLES LIKE 'general_log_file';
SHOW VARIABLES LIKE 'general_log';

SET GLOBAL general_log = 'on';
SET GLOBAL general_log_file = '/tmp/mysql.log';

//view the queries
sudo tail -f /tmp/mysql.log

The life of those settings is until MySQL is restarted. Or the laptop. So they are not permanent - which is great in my opinion - I just need them when I debug and I dont need to worry to edit the config then to remove them. If you dont remove the logging, it might grow too much if you forget about it.

Solution 4

I have created a Doctrine2 Logger that does exactly this. It "hydrates" the parametrized sql query with the values using Doctrine 2 own data type conversors.

<?php


namespace Drsm\Doctrine\DBAL\Logging;
use Doctrine\DBAL\Logging\SQLLogger,
    Doctrine\DBAL\Types\Type,
    Doctrine\DBAL\Platforms\AbstractPlatform;
/**
 * A SQL logger that logs to the standard output and
 * subtitutes params to get a ready to execute SQL sentence

 * @author  [email protected]
 */
class EchoWriteSQLWithoutParamsLogger implements SQLLogger

{
    const QUERY_TYPE_SELECT="SELECT";
    const QUERY_TYPE_UPDATE="UPDATE";
    const QUERY_TYPE_INSERT="INSERT";
    const QUERY_TYPE_DELETE="DELETE";
    const QUERY_TYPE_CREATE="CREATE";
    const QUERY_TYPE_ALTER="ALTER";

    private $dbPlatform;
    private $loggedQueryTypes;
    public function __construct(AbstractPlatform $dbPlatform, array $loggedQueryTypes=array()){
        $this->dbPlatform=$dbPlatform;
        $this->loggedQueryTypes=$loggedQueryTypes;
    }
    /**
     * {@inheritdoc}
     */
    public function startQuery($sql, array $params = null, array $types = null)

    {
        if($this->isLoggable($sql)){
            if(!empty($params)){
                foreach ($params as $key=>$param) {
                    $type=Type::getType($types[$key]);
                    $value=$type->convertToDatabaseValue($param,$this->dbPlatform);
                    $sql = join(var_export($value, true), explode('?', $sql, 2));
                }

            }
            echo $sql . " ;".PHP_EOL;
        }
    }

    /**
     * {@inheritdoc}
     */
    public function stopQuery()
    {

    }
    private function isLoggable($sql){
        if (empty($this->loggedQueryTypes)) return true;
        foreach($this->loggedQueryTypes as $validType){
            if (strpos($sql, $validType) === 0) return true;
        }
        return false;
    }
}

Usage Example:; The following peace of code will echo on standard output any INSERT,UPDATE,DELETE SQL sentences generated with $em Entity Manager,

/**@var  \Doctrine\ORM\EntityManager $em */
$em->getConnection()
                ->getConfiguration()
                ->setSQLLogger(
                    new EchoWriteSQLWithoutParamsLogger(
                        $em->getConnection()->getDatabasePlatform(),
                        array(
                            EchoWriteSQLWithoutParamsLogger::QUERY_TYPE_UPDATE,
                            EchoWriteSQLWithoutParamsLogger::QUERY_TYPE_INSERT,
                            EchoWriteSQLWithoutParamsLogger::QUERY_TYPE_DELETE
                        )
                    )
                );

Solution 5

getSqlQuery() does technically show the whole SQL command, but it's a lot more useful when you can see the parameters as well.

echo $q->getSqlQuery();
foreach ($q->getFlattenedParams() as $index => $param)
  echo "$index => $param";

To make this pattern more reusable, there's a nice approach described in the comments at Raw SQL from Doctrine Query Object.

View more solutions

Comments

[Marek]

Best way I've found to see full query is described in this answer: stackoverflow.com/a/678310/229077

[Vincent Pazeller]

You can take advantage of the work done by Doctrine (the profiler is displaying a runnable query). See my answer below for details

[Justin Finkelstein]

Whilst it works as variable assignments, you might want to consider this: print $query->getSQL(); foreach ($query->getParameters() as $param) { print "{$param->getName()} -> {$param->getValue()}\n"; } as you'll get a more readable output

[Matthieu Napoli]

Pascal: you shouldn't say it's not a "real SQL query" because prepared statement are real SQL query, it's just that the parameters as sent out separately. This wording could confuse people (e.g. olivierpons.fr/2014/03/22/symfony-2-avantages-et-inconvenien‌​ts).

[HaveNoDisplayName]

do not forget to add description with your Answer? Just one liner without description, not acceptable.

[Jaydeep Patel]

To print out sql query in Doctrine use $query->getResult()->getSql(); Thanks

[HaveNoDisplayName]

instead of adding commnet, Edit your answer

[gondo]

$query->getParameters(); will NOT return parameters in correct order, as they should appear in prepared query statement

[Darius.V]

it give littel benefit. When I copy the sql, I still have search wichi parameter where to insert manually, it takes ton of time. We want a query with inserted parameterrs, why we cannot find it so long? Even in codeigniter framework as far as I remember, in the profiler you could copy the query and run instantly without manually. We need same on symfony.

[Developer]

$query->getFlattenedParams(); not exist

[DarkMukke]

You should add some text to your answer explaining what the code does.

[Fahim]

very nice. works with normal queries but I have got a query with regexp and looks like does not support $qb = $this->createQueryBuilder('r') ->innerJoin('r.profile', 'p') ->addSelect('p') ->where('REGEXP(:fileNamePattern, r.fileNamePattern) = 1') ->andWhere('p.incomingLocation = :incomingLocation') ->setParameters([ 'fileNamePattern' => $fileName, 'incomingLocation' => $location ])->getQuery();

[Darius.V]

I think here author of question did not care what doctrine sends or not. What user and I wanted to know is how to get query which we can copy paste and run without having to manually replaces question marks with parameters. Like in codeigniter. I think I had found this in symfony debugger, but I still cannot find when I run script from command line.

[Darius.V]

Does not work with all queries. When I had this ->setParameters(array( 'insuranceCarrier' => $insuranceCarrier, 'dateFrom' => $dateFrom->format('Y-m-d'), 'dateTo' => $dateTo->format('Y-m-d'), )) those were left with ? marks in sql.

[Darius.V]

Does not work when parameters are date strings like '2019-01-01'

[k00ni]

I know this is an old post, but both of your links lead to a 404 page. Can you update your answer please? I am asking, because i am not sure what you mean with $q. It doesn't seem to be the query nor the query builder.

[ladenedge]

I'm afraid I can't find the more reusable code. $q in this case is a Doctrine 1 query. You may be using Doctrine 2, in which case you'll want something like $qb = $this->createQueryBuilder('a'); $q = $qb->getQuery(); $sql = $q->getSQL(); $params = $q->getParameters(); Hopefully that helps!

[Erdal G.]

Just found also how to use this with API calls. Just look at the response header for x-debug-token-link, it's the URL of the associated profiler page :)

[hdorio]

@Developer for newer version of Doctrine you can replace getFlattenedParams() by getParameters(). Also one may find more useful getSQL() instead of getDql().

[Asenar]

This helps a lot ! I just take your code and put it in a function executeAndReturnRealQuery($query). The only changes I made is I directly use an instance of DoctrineExtension, then return $doctrineExtension->replaceQueryParameters($params['query'][‌​'sql'], $params['query']['sql']);

[user2342558]

This solution is poor... ti's better to get the real full query from code.