18

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?

twigmac
  • 1,772
  • 3
  • 21
  • 38

2 Answers2

22

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.

Kerem
  • 11,377
  • 5
  • 59
  • 58
halfer
  • 19,824
  • 17
  • 99
  • 186
  • 2
    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` – Jordan Kasper May 02 '13 at 15:28
  • @jakerella: can you expand on that in an answer? I am not sure that makes it clear how to do what you're suggesting. – halfer May 02 '13 at 15:33
  • @halfer 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. – Jordan Kasper May 02 '13 at 15:42
11

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 [] []
Paul T. Rawkeen
  • 3,994
  • 3
  • 35
  • 51
  • 1
    Very promising suggestion! I'm looking forward to trying this out. – twigmac Nov 14 '14 at 05:08
  • 3
    Note this command won't output anything unless useDebug is true: $con = Propel::getConnection(); $con->useDebug(true); $con->getLastExecutedQuery(); – bbird Nov 21 '14 at 17:24