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')
    );

Solution 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.

Solution 2

One more thing you can do is ....

Go to Cake/Model/DataSource/DboSource.php and locate function execute() and print $sql variable. That should print the sql.

This certainly is not be the cleanest way (as you are changing Cake directory) .. but certainly would be quickest just to debug if something is not working with sql.

Solution 3

Try...
function getLastQuery($model) {
    $dbo = $model->getDatasource();
    $logData = $dbo->getLog();
    $getLog = end($logData['log']);
    echo $getLog['query'];
}

Solution 4

Simple way to show all executed query of your given model:

  $sqllog = $this->ModelName->getDataSource()->getLog(false, false);       
  debug($sqllog);

Solution 5

class YourController extends AppController {
    function testfunc(){
        $this->Model->find('all', $options);
        echo 'SQL: '.$this->getLastQuery();
    }

    function getLastQuery()
    {
        $dbo = ConnectionManager::getDataSource('default');
        $logs = $dbo->getLog();
        $lastLog = end($logs['log']);
        return $lastLog['query'];
    }
}

or you can get all the query by adding following line in to the function execute() in lib/Cake/Model/DataSource.php

Debugger::dump($sql);

View more solutions