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

My solution:

 /**
 * Get SQL from query
 * 
 * @author Yosef Kaminskyi 
 * @param QueryBilderDql $query
 * @return int
 */
public function getFullSQL($query)
{
    $sql = $query->getSql();
    $paramsList = $this->getListParamsByDql($query->getDql());
    $paramsArr =$this->getParamsArray($query->getParameters());
    $fullSql='';
    for($i=0;$i<strlen($sql);$i++){
        if($sql[$i]=='?'){
            $nameParam=array_shift($paramsList);

            if(is_string ($paramsArr[$nameParam])){
                $fullSql.= '"'.addslashes($paramsArr[$nameParam]).'"';
             }
            elseif(is_array($paramsArr[$nameParam])){
                $sqlArr='';
                foreach ($paramsArr[$nameParam] as $var){
                    if(!empty($sqlArr))
                        $sqlArr.=',';

                    if(is_string($var)){
                        $sqlArr.='"'.addslashes($var).'"';
                    }else
                        $sqlArr.=$var;
                }
                $fullSql.=$sqlArr;
            }elseif(is_object($paramsArr[$nameParam])){
                switch(get_class($paramsArr[$nameParam])){
                    case 'DateTime':
                             $fullSql.= "'".$paramsArr[$nameParam]->format('Y-m-d H:i:s')."'";
                          break;
                    default:
                        $fullSql.= $paramsArr[$nameParam]->getId();
                }

            }
            else                     
                $fullSql.= $paramsArr[$nameParam];

        }  else {
            $fullSql.=$sql[$i];
        }
    }
    return $fullSql;
}

 /**
 * Get query params list
 * 
 * @author Yosef Kaminskyi <[email protected]>
 * @param  Doctrine\ORM\Query\Parameter $paramObj
 * @return int
 */
protected function getParamsArray($paramObj)
{
    $parameters=array();
    foreach ($paramObj as $val){
        /* @var $val Doctrine\ORM\Query\Parameter */
        $parameters[$val->getName()]=$val->getValue();
    }

    return $parameters;
}
 public function getListParamsByDql($dql)
{
    $parsedDql = preg_split("/:/", $dql);
    $length = count($parsedDql);
    $parmeters = array();
    for($i=1;$i<$length;$i++){
        if(ctype_alpha($parsedDql[$i][0])){
            $param = (preg_split("/[' ' )]/", $parsedDql[$i]));
            $parmeters[] = $param[0];
        }
    }

    return $parmeters;}

Example of usage:

$query = $this->_entityRepository->createQueryBuilder('item');
$query->leftJoin('item.receptionUser','users');
$query->where('item.customerid = :customer')->setParameter('customer',$customer)
->andWhere('item.paymentmethod = :paymethod')->setParameter('paymethod',"Bonus");
echo $this->getFullSQL($query->getQuery());

Examples related to php

I am receiving warning in Facebook Application using PHP SDK Pass PDO prepared statement to variables Parse error: syntax error, unexpected [ Preg_match backtrack error Removing "http://" from a string How do I hide the PHP explode delimiter from submitted form results? Problems with installation of Google App Engine SDK for php in OS X Laravel 4 with Sentry 2 add user to a group on Registration php & mysql query not echoing in html with tags? How do I show a message in the foreach loop?

Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to orm

How to select specific columns in laravel eloquent Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment] How to query between two dates using Laravel and Eloquent? Laravel - Eloquent "Has", "With", "WhereHas" - What do they mean? How to Make Laravel Eloquent "IN" Query? How to auto generate migrations with Sequelize CLI from Sequelize models? How to fix org.hibernate.LazyInitializationException - could not initialize proxy - no Session Select the first 10 rows - Laravel Eloquent How to make join queries using Sequelize on Node.js What is Persistence Context?

Examples related to doctrine

How do I delete an entity from symfony2 Order by multiple columns with Doctrine How to use andWhere and orWhere in Doctrine? How to encode Doctrine entities to JSON in Symfony 2.0 AJAX application? Doctrine 2: Update query with query builder tar: Error is not recoverable: exiting now Doctrine2: Best way to handle many-to-many with extra columns in reference table Get the last insert id with doctrine 2? Execute raw SQL using Doctrine 2 Doctrine - How to print out the real sql, not just the prepared statement?

Examples related to doctrine-query

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