CODEWITHSUNDEEP
sqldebuggingloggingcakephp-1.3
yossi
yossi

Reputation: 3164

cakephp see the compiled SQL Query before execution

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

Upvotes: 17

Views: 48774

Answers (7)

palkesh
palkesh

Reputation: 11

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

enter image description here

And enter image description here

Upvotes: -1

palkesh
palkesh

Reputation: 11

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

echo $this->Payment->save();

Out put like =>SQL Query: INSERT INTO photoora_photoorange.payments VALUES (*******)

[insert query][2]

Upvotes: 0

Faisal
Faisal

Reputation: 4765

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

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

Upvotes: 3

Borislav Sabev
Borislav Sabev

Reputation: 4866

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.

Upvotes: 32

Sadee
Sadee

Reputation: 3180

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

Upvotes: 2

Ninad Desai
Ninad Desai

Reputation: 567

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.

Upvotes: 5

Indrajeet Singh
Indrajeet Singh

Reputation: 2989

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

Upvotes: 3

Related Questions