Snippet: Creating & Debugging Complex SQL queries in Symfony

When working with large and complex datasets in Symfony, there comes a point where the ORM layer (Propel or Doctrine) causes more problems than it solves. Sometimes it's necessary to debug large SQL queries built using the ORM layer, and at other times it's appropriate to bypass the ORM layer entirely.  The following snippets come from projects using Propel, I realise a lot of folks will have moved on to Doctrine but I hope there may be one or two useful things for that here too.

Executing raw SQL in Symfony without Propel Criteria:

$sql = "SELECT %s FROM %s WHERE id = %s";
$sql = sprintf($sql, BranchPeer::NAME, BranchPeer::TABLE_NAME, $branch->getId());

$con = Propel::getConnection(BranchPeer::DATABASE_NAME, Propel::CONNECTION_READ);
$stmt = $con->prepare($sql);
$stmt->execute();

$branches = array();

while($branch = $stmt->fetch(PDO::FETCH_ASSOC))
{
   $branches[] = $branch['name'];
}
return $branches;

If you're executing a statement which changes data in the database, rather than just retrieving it (i.e. UPDATE) you also need to run commit

]$stmt->commit();

Debugging PDO Statements

print_r($stmt->queryString);

Which outputs the raw query:

SELECT branch.name FROM branch WHERE id = 1

Debugging Propel Criteria

$c = new Criteria();
...
var_dump($c);

This outputs details of all the components of your query, so you can see which part might not be working correctly.

object(Criteria)[87]
  private 'ignoreCase' => boolean false
  private 'singleRecord' => boolean false
  private 'selectModifiers' => 
    array
      empty
  private 'selectColumns' => 
    array
      0 => string 'branch.ID' (length=9)
      1 => string 'branch.ROOT_ID' (length=14)
      2 => string 'branch.NAME' (length=11)
  private 'orderByColumns' => 
    array
      empty
  private 'groupByColumns' => 
    array
      empty
  private 'having' => null
  private 'asColumns' => 
    array
      empty
  private 'joins' => 
    array
      empty
  private 'dbName' => string 'propel' (length=6)
  private 'primaryTableName' => null
  private 'originalDbName' => null
  private 'limit' => int 0
  private 'offset' => int 0
  private 'blobFlag' => null
  private 'aliases' => 
    array
      empty
  private 'useTransaction' => boolean false
  private 'map' => 
    array
      'branch.ID' => 
        object(Criterion)[97]
          private 'value' => int 1
          private 'comparison' => string '=' (length=1)
          private 'table' => string 'branch' (length=6)
          private 'realtable' => string 'branch' (length=6)
          private 'column' => string 'ID' (length=2)
          private 'ignoreStringCase' => boolean false
          private 'db' => 
            object(DBMySQL)[95]
              ...
          private 'clauses' => 
            array
              ...
          private 'conjunctions' => 
            array
              ...
          private 'parent' => null

Perhaps more useful, is printing the result of Criteria's toString method

print_r($c->toString());

Which returns the query and it's parameters separately:

Criteria: SQL (may not be complete): SELECT branch.ID, branch.ROOT_ID, branch.NAME FROM `branch` WHERE branch.ID=:p1 Params: branch.ID => 1

Please let me know if you have any problems with these snippets, or if you find them useful!

A Note on Snippets: When using frameworks such as Symfony it is often the simplest pieces of code which are the hardest to either find or remember. These snippets are placed here for my own reference and will hopefully be useful to others. If you find them useful or have any suggestions, please let me know.