Propel: Get Raw SQL from Query object?

Question

How do I get the raw SQL statement from a query object in Propel? I need this for debugging purposes.

For example: I would like to have a function as in

$rawSql = new BookQuery::create()->filterById(25)->getRawSql();

Does something like this exist?

Shriver

Answer 1

Yes; you're after the toString method from the Criteria parent class:

$rawSql = (new BookQuery)::create()->filterById(25)->toString();

As @jakerella says, the specific values you use for filtering will be bound by the database engine, rather than Propel, and so you'll see the structure of the query but not exactly what will be executed. If you want to see that, then you can check your database query logs, if they're enabled.

Thacher

Note that you won't get specific select columns in the query above - Propel does that right before the find. Thus you must see something like: SELECT FROM book WHERE id=:p1; ... :p1=>25 – Ramp

@jakerella: can you expand on that in an answer? I am not sure that makes it clear how to do what you're suggesting. – Thacher

@Thacher your answer is correct, it's just that Propel binds the select columns at the last moment, so you may not see the selected columns in the SQL (notice in my example how there is nothing between SELECT and FROM). Propel doesn't use * to select all columns, it manually adds all necessary columns in each query. – Ramp

Answer 2

Fulfilling accepted answer, you can use next code afterwards query execution.

\Propel::getConnection()->getLastExecutedQuery() // Returns fully qualified SQL

It allows you to see the full query (including select columns and fetched parameters) which was sent to database.

---

UPD: (as mentioned by @bbird)

This command won't output anything unless useDebug is true:

\Propel::getConnection()->useDebug(true);

---

UPD2: (if you are using Symfony framework)

One more thing worth mentioning is PropelORM+Symfony.

If you need to trace SQL it is possible using logs. Propel has it's own monolog channel called propel and fully qualified queries are logged with DEBUG log level on relevant channel (propel.DEBUG).

Log/query record looks like this:

[2016-10-04 17:00:46] propel.DEBUG: time:  0.000 sec | mem:   24.8 MB | connection: default | SELECT `id`, `username`, `email`, `last_login` FROM `users` WHERE `id` = 123 [] []

Torso

Very promising suggestion! I'm looking forward to trying this out. – Shriver

Note this command won't output anything unless useDebug is true: $con = Propel::getConnection(); $con->useDebug(true); $con->getLastExecutedQuery(); – Medwin