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

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