cakephp see the compiled SQL Query before execution

Question

My query gets the timeout error on each run. Its a pagination with joins.
I want to debug the SQL, but since I get a timeout, I can't see it.

How can I see the compiled SQL Query before execution?

---

Some cake code:

$this -> paginate = array(
        'limit' => '16',
        'joins' => array( array(
                'table' => 'products',
                'alias' => 'Product',
                'type' => 'LEFT',
                'conditions' => array('ProductModel.id = Product.product_model_id')
            )),
        'fields' => array(
            'COUNT(Product.product_model_id) as Counter',
            'ProductModel.name'
            ),
        'conditions' => array(
            'ProductModel.category_id' => $category_id,
        ),
        'group' => array('ProductModel.id')
    );

Answer 1

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.