Cakephp See the Compiled SQL Query Before Execution

cakephp see the compiled SQL Query before execution

First off, set the debug variable to 2 in app/config/config.php.

Then add:

<?php echo $this->element('sql_dump');?>

at the end of your layout. This should actually be commented out in your default cake layout.

You will now be able see all SQL queries that go to the database.

Now copy the query and use the SQL EXPLAIN command (link is for MySQL) over the database to see what the query does in the DBMS. For more on CakePHP debugging check here.

Since your script doesn't even render you can try to get the latest log directly from the datasource with:

function getLastQuery()
{
    $dbo = $this->getDatasource();
    $logs = $dbo->getLog();
    $lastLog = end($logs['log']);
    return $lastLog['query'];
}

This needs to be in a model since the getDatasource() function is defined in a model.
Inspect the whole $logs variable and see what's in there.

what actually is ran through the query

Set app/core/config.php debug = 2

http://book.cakephp.org/view/924/The-Configuration-Class#CakePHP-Core-Configuration-Variables-931

CakePHP - the SQL log shows a working query which returns a row, but results variable is NULL?

Well, I feel like a bit of a chump...

The offending code was actually in the model file, client.php.

public function afterFind(){
    //todo
}

I was planning on writing some code here, but neglected to. afterFind needs to return an array of results. I wasn't doing so, hence all calls to Client::find (ie via ClientsController::paginate) were returning NULL, not even an empty array.

Getting raw SQL query string from PDO prepared statements

I assume you mean that you want the final SQL query, with parameter values interpolated into it. I understand that this would be useful for debugging, but it is not the way prepared statements work. Parameters are not combined with a prepared statement on the client-side, so PDO should never have access to the query string combined with its parameters.

The SQL statement is sent to the database server when you do prepare(), and the parameters are sent separately when you do execute(). MySQL's general query log does show the final SQL with values interpolated after you execute(). Below is an excerpt from my general query log. I ran the queries from the mysql CLI, not from PDO, but the principle is the same.

081016 16:51:28 2 Query       prepare s1 from 'select * from foo where i = ?'
                2 Prepare     [2] select * from foo where i = ?
081016 16:51:39 2 Query       set @a =1
081016 16:51:47 2 Query       execute s1 using @a
                2 Execute     [2] select * from foo where i = 1

You can also get what you want if you set the PDO attribute PDO::ATTR_EMULATE_PREPARES. In this mode, PDO interpolate parameters into the SQL query and sends the whole query when you execute(). This is not a true prepared query. You will circumvent the benefits of prepared queries by interpolating variables into the SQL string before execute().

---

Re comment from @afilina:

No, the textual SQL query is not combined with the parameters during execution. So there's nothing for PDO to show you.

Internally, if you use PDO::ATTR_EMULATE_PREPARES, PDO makes a copy of the SQL query and interpolates parameter values into it before doing the prepare and execute. But PDO does not expose this modified SQL query.

The PDOStatement object has a property $queryString, but this is set only in the constructor for the PDOStatement, and it's not updated when the query is rewritten with parameters.

It would be a reasonable feature request for PDO to ask them to expose the rewritten query. But even that wouldn't give you the "complete" query unless you use PDO::ATTR_EMULATE_PREPARES.

This is why I show the workaround above of using the MySQL server's general query log, because in this case even a prepared query with parameter placeholders is rewritten on the server, with parameter values backfilled into the query string. But this is only done during logging, not during query execution.

CakePHP executes method names as SQL queries

That's what I get for developing on Windows. Don't be a fool. Make sure your model name is all lowercase. Though I wish CakePHP warned me that it couldn't find the model.

Getting the last n results of a MySQL query but in CakePHP

If I understand correctly, I think you are on the right track.

Add this to your find parameters:

'order' => 'id DESC' // or created or whatever field you want to reverse sort by

That combination with LIMIT will get the last x results.

---

Related Topics