6

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)

Garbit
  • 5,805
  • 6
  • 39
  • 72

5 Answers5

12

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
  • 5,845
  • 4
  • 22
  • 21
  • 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. – Yzmir Ramirez May 23 '12 at 03:23
2

I believe this is the way 

$rawSql = BasePeer::createSelectSql( $criteria, $params );
Peter Bailey
  • 105,256
  • 31
  • 182
  • 206
  • 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... –  Sep 09 '09 at 14:18
  • in that $rawSql I must substitute parameters :p1, :p2, etc. But in this way I must write already written code –  Sep 09 '09 at 14:23
  • 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 Sep 09 '09 at 14:26
  • No, $params used for returning parameters from BasePeer::createSelectSql –  Sep 09 '09 at 14:28
  • 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? – Peter Bailey Sep 09 '09 at 14:32
  • $params is empty before BasePeer::createSelectSql, after - http://pastebin.com/m19325813 –  Sep 09 '09 at 14:38
  • 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... –  Sep 09 '09 at 14:44
  • 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 Sep 09 '09 at 15:04
1

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.

Dopey
  • 11
  • 1
0

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.

0

Even easier try:

print($criteria->toString()) ;
mikesl
  • 2,133
  • 20
  • 25