1

I need to track where in my CakePHP 2.5.2 app this query is being executed (as reported by DebugKit):

SELECT COUNT(*) AS count FROM sometable WHERE status = 1

I have found a model where the table is referenced, and maybe it is referenced in other places.

Is there a better way other than just looking for models or running every possible PHP script to find where this query came from in my CakePHP app?

Inigo Flores
  • 4,461
  • 1
  • 15
  • 36
Ângelo Rigo
  • 2,039
  • 9
  • 38
  • 69
  • This is probably a query cakephp runs internally. Perhaps you have a query similar to this without the count thing. – pedrofs Jan 14 '16 at 13:20
  • Must be some internal query, all the models and controllers i see does not trigger this query. I would like to discover where it happens and change to a more performant query . – Ângelo Rigo Jan 14 '16 at 13:33

1 Answers1

1

You can extend the Mysql class and override logQuery() so that it logs every request with the logging mechanism of your choice.

Example:

  1. Create the file /app/Model/Datasource/MysqlLog.php and add the following:

    App::uses('Mysql', 'Model/Datasource/Database');

class MysqlLog extends Mysql {    

    function logQuery($sql, $params = []) {
        parent::logQuery($sql);
        Debugger::log("sql[{$this->_queriesCnt}]: $sql");
    }
}

  2. Instruct CakePHP to use the new class by modifying your /app/Config/database.php:

    public $default = array(
    'datasource' => 'Database/MysqlLog',
    'persistent' => true,
    'host' => 'localhost',
    'login' => 'my_login',
    'password' => 'my_pass',
    'database' => 'my_db',
);

Requests will be logged to /app/tmp/logs/debug.log.

Based on the post Logging sql queries into file in CakePHP 2 by Vladimir Bilyov.

Inigo Flores
  • 4,461
  • 1
  • 15
  • 36
  • Is there any property i can set into the model so this count will use just one field to perform better ? – Ângelo Rigo Jan 14 '16 at 13:57
  • 1
    First of all you need to find out where it's coming from. If it has to do with pagination, perhaps [this](http://book.cakephp.org/2.0/en/core-libraries/components/pagination.html#custom-query-pagination) helps. However, `count(*)` should be quicker than `count(field)`. See [this answer](http://stackoverflow.com/a/3003477/3144159). – Inigo Flores Jan 14 '16 at 14:07
  • 1
    It should be possible, if you apply the same principle. But I'm not sure that Oracle is fully supported by CakePHP. See this question - http://stackoverflow.com/questions/14376743/using-oracle-database-with-cakephp-2-0 – Inigo Flores Jan 22 '16 at 10:52