How to See Cakephp's SQL Dump in the Controller

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.

Displaying queries in CakePHP without running them

Yes you can, have a look at "Transactions" http://book.cakephp.org/2.0/en/models/transactions.html

// get the datasource and store it in a local variable
$ds = $this->MyModel->getDataSource();
// begin a "transaction"
$ds->begin();
// do your saving
$this->MyModel->saveAll($rows); // you can add more queries here, that's what transactions are all about! :)
// rollback, in a normal situation you would check if the save was successful and commit()/rollbac() depending on the situation.
$ds->rollback();

Please note: Auto Increment fields WILL increment, due to the fact that MySQL or any other database engine will "reserve" these ID's while doing the transaction in order to prevent duplicated ID's. This shouldn't be of any concern, but when you are debugging and you are remembering an ID , it could give you a headache if it's Monday morning (been there, done that)... ;-)

cakephp v2. hide sql dump shown at the end of page

in app/Config/core.php file, just change

Configure::write('debug', 2);

To

Configure::write('debug', 0);

Alternative

Remove or comment

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

in app/View/Layouts/default.ctp

CakePhp 2.x print sql query On Ajax Request

Try this

debug($this->YOUR_MODEL_NAME->getDataSource()->getLog(false, false));

Show SQL query of controller

You can turn on query logging on CakePHP3: http://book.cakephp.org/3.0/en/orm/database-basics.html#query-logging

If you're interested in doing more debugging and such, DebugKit does an amazing job.

How to get SQL Query on model- save() in CakePHP 3?

A solution could be using query logging

http://book.cakephp.org/3.0/en/orm/database-basics.html#query-logging

you can turn query logging on just when you save your model and then turn it off

For example I have a Comments model

In my bootstrap.php I did

Log::config('current', 
[ 
    'className' => 'File', 
    'path' => LOGS.date('Y-m').DS, // you don't need a DS between LOGS and date()
    'scopes' => ['daily','queriesLog'], 
    'file' => date('Y-m-d'), 
]);

and in my controller I did

$conn = \Cake\Datasource\ConnectionManager::get('default');
$comment = $this->Comments->get(5620); // 5620 is the id of one comments of mine
$conn->logQueries(true);
$comment = $this->Comments->get(5619); // 5619 is onother id of one comments of mine
$comment->text = 'Test';
$this->Comments->save($comment);
$conn->logQueries(false);

In this way a file is created in the logs folder and the file contains the following

2015-12-16 13:38:35 Debug: SELECT ... WHERE Comments.id = 5619 LIMIT 1
2015-12-16 13:38:35 Debug: BEGIN
2015-12-16 13:38:35 Debug: UPDATE comments SET text = 'Test' WHERE id = 5619
2015-12-16 13:38:35 Debug: COMMIT

note that the query used to get comment #5620 has not been logged

Also note that this works if you don't have debugkit enabled

Where is the log file after enabling query logging?

I've created a test project. Created a simple model so I can parse the data.

In the controller, I added these namespaces:

use App\Model\Table\User; // <---My model
use Cake\ORM\TableRegistry;
use Cake\Log\Log;
use Cake\Datasource\ConnectionManager;

Here's the basic data parse in a controller:

    $conn = ConnectionManager::get('default');
    Log::config('queries', [
        'className' => 'File',
        'path' => LOGS,
        'file' => 'queries.log',
        'scopes' => ['queriesLog']
    ]);

    $users = TableRegistry::get('User'); 

    $conn->logQueries(true);
    $q = $users->find('all');
    $results = $q->all();
    $conn->logQueries(false);

All of this works just great.

---

Related Topics