How can I see CakePHP's SQL dump in the controller?

Is there a way that one can cause CakePHP to dump its SQL log on demand? I'd like to execute code up until a point in my controller and see what SQL has been run.

Try this:

$log = $this->Model->getDataSource()->getLog(false, false);
debug($log);

http://api.cakephp.org/2.3/class-Model.html#_getDataSource

You will have to do this for each datasource if you have more than one though.

There are four ways to show queries:

1. This will show the last query executed of user model:

   debug($this->User->lastQuery());  
   

2. This will show all executed query of user model:

   $log = $this->User->getDataSource()->getLog(false, false);       
   debug($log);
   

3. This will show a log of all queries:

   $db =& ConnectionManager::getDataSource('default');
   $db->showLog();
   

4. If you want to show all queries log all over the application you can use in view/element/filename.ctp.

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

If you're using CakePHP 1.3, you can put this in your views to output the SQL:

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

So you could create a view called 'sql', containing only the line above, and then call this in your controller whenever you want to see it:

$this->render('sql');

(Also remember to set your debug level to at least 2 in app/config/core.php)

Source

for cakephp 2.0 Write this function in AppModel.php

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

To use this in Controller Write : echo $this->YourModelName->getLastQuery();

It is greatly frustrating that CakePHP does not have a $this->Model->lastQuery();. Here are two solutions including a modified version of Handsofaten's:

1. Create a Last Query Function

To print the last query run, in your /app_model.php file add:

function lastQuery(){
	$dbo = $this->getDatasource();
	$logs = $dbo->_queriesLog;
	// return the first element of the last array (i.e. the last query)
	return current(end($logs));
}

Then to print output you can run:

debug($this->lastQuery()); // in model

OR

debug($this->Model->lastQuery()); // in controller

2. Render the SQL View (Not avail within model)

To print out all queries run in a given page request, in your controller (or component, etc) run:

$this->render('sql');

It will likely throw a missing view error, but this is better than no access to recent queries!

(As Handsofaten said, there is the /elements/sql_dump.ctp in cake/libs/view/elements/, but I was able to do the above without creating the sql.ctp view. Can anyone explain that?)

In CakePHP 1.2 ..

$db =& ConnectionManager::getDataSource('default');
$db->showLog();

What worked finally for me and also compatible with 2.0 is to add in my layout (or in model)

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

It is also depending on debug variable setted into Config/core.php

Plugin DebugKit for cake will do the job as well. https://github.com/cakephp/debug_kit

If you are interested in some specific part of code, you can clear first the log, and then display only queries that happen after that point.

Also note that 'Model' below, is the actual class name, like User, Page etc.

//clear log (boolean $clear = true)
$this->Model->getDataSource()->getLog(false, true);
...
...
...
...
//Show log so far
$log = $this->Model->getDataSource()->getLog(false, false);
debug($log);
exit;