Lithium: Can we see queries executed as shown in CakePHP

Question

Do you know of any method to show the queries being executed in Lithium as it does in CakePHP. It may become easier for me to find what is executed. At the bottom of the page it shows the queries executed.

![The screen shot] http://imgur.com/ffNfQ

After I received the answer, I added the code to my controller:

Volumes::applyFilter('find', function($self, $params, $chain) {
      echo '<pre>===== self ======<br>';
      var_dump($self);
      echo '===== params ======<br>';
      var_dump($params);
      echo '===== chain ======<br>';
      var_dump($chain);
      echo '</pre>';
      $next = $chain->next($self, $params, $chain);

      return $next;

});

It gives me an output as to all var_dump of self and params, but I require the SQL query which gets executed.

Please check the screen shot http://imgur.com/ffNfQ

Please check my updated answer bellow, the second filter will allways output the exact SQL and not just the parameters that builds it. Please comment if you have any question. — Nils, Nov 10 '12 at 18:46

Nils · Answer 1 · 2012-11-10T18:44:13.590

You can do this by adding filters to the types of queries you wish to log. You can define them globaly in the bootstrap files our you can define them "localy" in the controllers. The example bellow does not print the query nice, however that is pretty easy to do when you have the data.

The code bellow shows how to get the information, it does not output it in a pretty way.

   Connections::get('default')->applyFilter('read', function($self, $params, $chain) {

        echo "===========</br>\n"; 
        if (is_object($params['query'])){
            var_dump($params['query']->conditions());
        }
        else{
            echo 'Query: '; var_dump($params['query']); echo "</br>\n";
        }
        $time = microtime(true);

        $next = $chain->next($self, $params, $chain);

        $time = microtime(true) - $time;            
        echo "Results: " . count((array)$next) . "</br>\n";
        echo "Time: " . $time . "</br>\n";

        return $next;

    });

Please note that echo it the data directly from the filter will render your page useless as this will be printed before headers. However if you save the data in the filter to an array instead of outputting it you can use it later in your views. And you can for instance make a template to add this information to the page.

Edit: updated code

Edit2: added a way to always get the exact SQL that is executed. To get the

Connections::get('default')->applyFilter('_execute', function($self, $params, $chain) {
    echo "SQL: " . $params['sql'] . "</br>\n";
    $time = microtime(true);

    $next =  $chain->next($self, $params, $chain);

    $time = microtime(true) - $time;
    $count = 0;
    while ($next->next())
     $count++;
    $next->rewind();
    echo "Count: " . $count . "</br>\n";        
    echo "Time: " . $time . "</br>\n";
    echo "=====================</br>\n";

    return $next;
});

Please check my question again. I want the result to show the queries fired to the DB. — Nilam Doctor, Nov 10 '12 at 13:06
I have added my answer from your research. Thanks for your suggestions. Now you too can contribute to the plugin https://github.com/nilamdoc/li3_show — Nilam Doctor, Nov 12 '12 at 20:47

score 1 · Answer 2 · answered Nov 10 '12 at 10:47

1

as @Nils suggested, you can leverage Lithium filters and write a simple queries logger.
I made one here. It logs the read queries to a file on a production environment.
You should be able to customize, and add filters on create, update and delete operations to fit your needs.

If you are looking to an out-of-the-box solution like Cake's debug toolbar, check the li3_perf project: https://github.com/tmaiaroto/li3_perf

answered Nov 10 '12 at 10:47

Mehdi Lahmam B.

2,240
16
22

I have tried li3_pref it also gives vardump, I am looking for the output which shows the actual query fired to the DB. – Nilam Doctor Nov 10 '12 at 13:11
The filters I wrote shows the actual query sent to the database – Mehdi Lahmam B. Nov 10 '12 at 17:55
@MehdiLahmamB. Love filters, there are so many ways to do similar things. I liked the gist and I need to look a bit more on the Logger. – Nils Nov 10 '12 at 18:49
I have added my answer from your research. Thanks for your suggestions. Now you too can contribute to the plugin github.com/nilamdoc/li3_show – Nilam Doctor Nov 12 '12 at 20:48

score 0 · Accepted Answer · answered Nov 12 '12 at 20:38

Based on all the suggestions by Nils and Mehdi I created a fresh plugin which will only show the query results and not the vardump or print_r() for the queries executed in Lithium with MongoDB.

It really now has become easier for me to find what it being executed.

You can take a look at the project at Github:

https://github.com/nilamdoc/li3_show

Output of li3_show

Lithium: Can we see queries executed as shown in CakePHP

3 Answers3

Linked

Related