On one of our projects that I am working on I had the following problem: I needed to create an aggregate temporary table in the database from a few different queries while still using Doctrine2. I needed to aggregate the results in the database rather than memory as the result set could be very large causing the PHP process to run out of memory. The reason I wanted to still use Doctrine to get the base queries was the application passes around a QueryBuilder object to add restrictions to the query which may be defined outside of the current function, every query in the application goes through this process for security purposes.
After looking around a bit, it was clear that Doctrine did not support (and shouldn’t support) what I was trying to do. My next step was to figure out how to get an executable query from Doctrine2 without ever running it. Doctrine2 has a built in SQL logger interface which basically lets you to listen for executed queries and to see what the actual SQL and parameters were for the executed query. The problem I had was I didn’t want to actually execute the query I had built in Doctrine, I just wanted the SQL that would be executed via PDO. After digging through the code a bit further I found the routines that Doctrine used to actually build the query and parameters for PDO to execute, however, the methods were all private and internalized. I came up with the following class to take a Doctrine Query and return a SQL statement, parameters, and parameter types that can be used to execute it via PDO.
| <?php | |
| namespace Example\Doctrine\Util; | |
| use Doctrine\ORM\Query; | |
| class QueryUtils | |
| { | |
| /** | |
| * @param Query $query | |
| * @return array An array with 3 indexes, sql the SQL statement with parameters as ?, params the ordered parameters, and paramTypes as the types each parameter is. | |
| */ | |
| public static function getRunnableQueryAndParametersForQuery(Query $query) | |
| { | |
| $sql = $query->getSQL(); | |
| $c = new \ReflectionClass('Doctrine\ORM\Query'); | |
| $parser = $c->getProperty('_parserResult'); | |
| $parser->setAccessible(true); | |
| /** @var \Doctrine\ORM\Query\ParserResult $parser */ | |
| $parser = $parser->getValue($query); | |
| $resultSet = $parser->getResultSetMapping(); | |
| // Change the aliases back to what was originally specified in the QueryBuilder. | |
| $sql = preg_replace_callback('/AS\s([a-zA-Z0-9_]+)/',function($matches) use($resultSet) { | |
| $ret = 'AS '; | |
| if($resultSet->isScalarResult($matches[1])) | |
| $ret.=$resultSet->getScalarAlias($matches[1]); | |
| else | |
| $ret.=$matches[1]; | |
| return $ret; | |
| },$sql); | |
| $m = $c->getMethod('processParameterMappings'); | |
| $m->setAccessible(true); | |
| list($params,$types)= $m->invoke($query,$parser->getParameterMappings()); | |
| return ['sql' => $sql, 'params' => $params,'paramTypes' => $types]; | |
| } | |
| } |
by | <?php | |
| // Example Usage | |
| // Get a Query Builder | |
| $qb = $this->getDoctrine() | |
| ->getRepository('AppBundle:Example') | |
| ->getQueryBuilderForParams($params); | |
| $queryInfo = QueryUtils::getRunnableQueryAndParametersForQuery($qb->getQuery()); | |
| // Use the query to insert to a temp table. | |
| $this->getDoctrine() | |
| ->getManager('default') | |
| ->getConnection() | |
| ->executeQuery("INSERT INTO my_temp_table (col1,col2,col3,col4) ".$queryInfo['sql'], | |
| $queryInfo['params'], | |
| $queryInfo['paramTypes']); |
In the ExampleUsage.php file above I take a query builder, get the runnable query, and then insert it into my temporary table. In my circumstance I had about 3-4 of these types of statements.
If you look at the QueryUtils::getRunnableQueryAndParametersForQuery function, it does a number of things.
- First, it uses Reflection Classes to be able to access private member of the Query. This breaks a lot of programming principles and Doctrine could change the interworkings of the Query class and break this class. It’s not a good programming practice to be flipping private variables public, as generally they are private for a reason.
- Second, Doctrine aliases any alias you give it in your select. For example if you do “SELECT u.myField as my_field” Doctrine may realias that to “my_field_0”. This make it difficult if you want to read out specific columns from the query without going back through Doctrine. This class flips the aliases back to your original alias, so you can reference ‘my_field’ for example.
- Third, it returns an array of parameters and their types. The Doctrine Connection class uses these arrays to execute the query via PDO. I did not want to reimplement some of the actual parameters and types to PDO, so I opted to pass it through the Doctrine Connection class.
Overall this was the best solution I could find at the time for what I was trying to do. If I was ok with running the query first, capturing the actual SQL via an SQL Logger would have been the proper and best route to go, however I did not want to run the query.
Hope this helps if you find yourself in a similar situation!