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:
For those days when Propel Criteria just isn’t cutting it. Particularly with HAVING and other sub queries that can be quite cumbersome.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | $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
1 | $stmt->commit(); |
Debugging PDO Statements
This is a simple trick that I always forget:
1 |
Which outputs the raw query:
SELECT branch.name FROM branch WHERE id = 1
Debugging Propel Criteria
Printing or “vardumping” an entire Criteria can be useful to identify problems with complex queries built using propel.
1 2 3 |
This outputs details of all the components of your query, so you can see which part might not be working correctly.
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
1 |
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.
24th Aug
benbrahim says:
thanks for your post, i wish to add a like, to produce this sql like this : select branch.ROOT_ID, branch.NAME FROM `branch` WHERE branch.NAME like ‘%all%’ ;
any idea ?!!!
this is my code
$sql = ‘select ‘. self::NAME .” “. self::LASTNAME.” from ” .self::TABLE_NAME .” where “.self::NAME .” like ‘%a%’ ” ;
but i have error in ‘%a%’ do you now why !!!!
thanks
24th Aug
ErisDS says:
At first glance, the only problem I can see is that your quotes are all mixed up.
You need to pay special attention to these, use spacing properly to make it easier to read and you will spot those mistakes more easily:
Not sure if you’re using doctrine or propel as you didn’t say, but with Propel Criteria there is no reason why you would need to use SQL to create this query. Instead use:
25th Aug
benbrahim says:
i’am using propel but i wish using this type of query $sql = ‘select ‘. self::NAME .” “. self::LASTNAME.” from ” .self::TABLE_NAME .” where “.self::NAME .” like .‘%a%’ ” ;
26th Aug
ErisDS says:
Try copying my code snippet from my earlier comment where I have matched all the quotes properly – yours are all mixed up. Also you are missing the comma between your select fields.
Try
as well – that should help show you where the mistakes are.
26th Aug
benbrahim says:
thanks you, i fund the mistak i’m added \” in like condition
$sql = ‘select ‘. self::NAME .” “. self::LASTNAME.” from ” .self::TABLE_NAME .” where “.self::NAME .” like \”%a%\” ” ;
26th Aug
ErisDS says:
Well that still doesn’t have a comma between your SELECT items, and the quotes are all different – look at the ones around “select” and then look at the other ones… they are not the same.