Hi all! How can I compile Propel Criteria to clear SQL? I've tried $criteria->toString(); but this is not I expected. Also I've tried ModelPeer::doSelectStmt($criteria) but it returned raw sql (required parameters substitution)
+1
A:
I believe this is the way
$rawSql = BasePeer::createSelectSql( $criteria, $params );
Peter Bailey
2009-09-09 14:13:12
As I said earlier I want to get clear sql, not raw (such as "select from article where NAME=:p1")I guess Propel provides such feature...
smileua
2009-09-09 14:18:51
in that $rawSql I must substitute parameters :p1, :p2, etc.But in this way I must write already written code
smileua
2009-09-09 14:23:38
That's what the params array is for. I guess I didn't make that clear - supply your params as an associative array.
Peter Bailey
2009-09-09 14:26:27
No, $params used for returning parameters from BasePeer::createSelectSql
smileua
2009-09-09 14:28:52
Peter Bailey
2009-09-09 14:32:25
Criteria before BasePeer::createSelectSql http://pastebin.com/m796cbe04
smileua
2009-09-09 14:37:19
$params is empty before BasePeer::createSelectSql, after - http://pastebin.com/m19325813
smileua
2009-09-09 14:38:51
I see the Peer classes are using PDOStatement. Peer class compiles the criteria to http://pastebin.com/m5047760. Then it asked the instance of PDOStatement to prepare the statement and then Peer class binds those values by PDOStatement::bindValue and then executes. I don't know how to ask the PDOStatement object the clear SQL query before PDOStatement::execute...
smileua
2009-09-09 14:44:29
After digging around in the creole codebase, I found the method that it uses for this process, which is `PreparedStatementCommon::replaceParams()`, which unfortunately is protected so you don't have access to it from your scripts. In short, i'm not sure you CAN do this without doing the replacing yourself, or throwing the Decorator Pattern at this problem.
Peter Bailey
2009-09-09 15:04:08
humm, okay, thank you very much!!!
smileua
2009-09-09 15:14:47
+3
A:
First of all, it's important to note that Propel uses PDO with prepared statements, so you're not going to get a fully "built-out" SQL statement in PHP. Using the Criteria->toString() is a good start, but as Peter mentions a lot of the work is indeed done by the BasePeer::createSelectSql() method.
Here's the most complete way (from Propel) to see what the SQL will look like (with placeholders) and the parameters that will be substituted in:
$params = array(); // This will be filled with the parameters
$sql = BasePeer::createSelectSql($criteria, $params);
print "The raw SQL: " . $sql . "\n";
print "The parameters: " . print_r($params, true) . "\n";
Note that you may get better mileage from just logging the queries at the database level. Of course, if PDO is configured (or supports) to use native db prepared statements, then you may still be seeing placeholders in the db too.
Hans L
2009-09-18 11:03:09
A:
I decided to work around. Actually I needed the INSERT INTO ... SELECT. I.e - create SELECT statement by means of Criteria, further append INSERT INTO and execute.
So I asked BasePeer to create raw sql (BasePeer::createSelectSql), then appended INSERT INTO ahead. Since I need populate statement's values (:p1, :p2, etc), but method BasePeer::populateStmtValues is private (why?) I had to copy'paste that method to another place and call it.
smileua
2009-09-19 15:32:33
A:
We had the same problem recently. See http://groups.google.com/group/propel-development/browse%5Fthread/thread/f56a5a8ee5db3b60
Now BasePeer::populateStmtValues() is public from propel version 1.4 onwards. This is currently in dev.
Dopey
2009-09-23 14:42:17