How to compile Propel Criteria to SQL

Question

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)

Levorotation

Answer 1

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.

Instructive

I added a wordwrap($sql) so really long sql can fit on the page. I was converting some Propel queries back to PDO and this worked like a charm. Thanks @Hans L. – Liew

Answer 2

I believe this is the way

$rawSql = BasePeer::createSelectSql( $criteria, $params );

Retene

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... – Levorotation

in that $rawSql I must substitute parameters :p1, :p2, etc. But in this way I must write already written code – Levorotation

That's what the params array is for. I guess I didn't make that clear - supply your params as an associative array. – Retene

No, $params used for returning parameters from BasePeer::createSelectSql – Levorotation

Then I guess I misunderstand the documentation. It says ` * @param array &$params Parameters that are to be replaced in prepared statement.` Can you show me what your criteria object looks like? – Retene

$params is empty before BasePeer::createSelectSql, after - pastebin.com/m19325813 – Levorotation

I see the Peer classes are using PDOStatement. Peer class compiles the criteria to 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... – Levorotation

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. – Retene

Answer 3

We had the same problem recently. See http://groups.google.com/group/propel-development/browse_thread/thread/f56a5a8ee5db3b60

Now BasePeer::populateStmtValues() is public from propel version 1.4 onwards. This is currently in dev.

Centiliter

Answer 4

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.

Levorotation

Answer 5

Even easier try:

print($criteria->toString()) ;

Sac