6

Is there some way of logging or outputting the mysql query string that is generated from a Model_Orm::query() or a Model_Orm::find()?

I have a complex Orm::query() that is not giving me the results i'd expect, and i'm wondering why!

stef
  • 1,446
  • 1
  • 17
  • 26

3 Answers3

10

After doing anything which runs a query be it any of the following

\Model_Something::find('all');
\Model_Something::query()...
\DB::query()...
\DB::select()...

You can use

echo \DB::last_query();

To see what was last run by whichever means you used to query the database.

Working example taken from production code:

$product = Model_Product::find('all', array(
     'related' => array(
              'i18ns',
     ),
     'where' => array(
              array('i18ns.variant_slug', $slug)
     )
));

echo \DB::last_query();
exit;

SELECT `t0`.`family_id` AS `t0_c0`, `t0`.`type_id` AS `t0_c1`,
       `t0`.`sort_weight` AS `t0_c2`, `t0`.`active` AS `t0_c3`,
       `t0`.`attribute_set` AS `t0_c4`, `t0`.`created_at` AS `t0_c5`,
       `t0`.`updated_at` AS `t0_c6`, `t0`.`site_id` AS `t0_c7`,
       `t0`.`temporal_start` AS `t0_c8`, `t0`.`temporal_end` AS `t0_c9`,
       `t0`.`id` AS `t0_c10`, `t1`.`id` AS `t1_c0`, `t1`.`product_id` AS `t1_c1`,
       `t1`.`language_id` AS `t1_c2`, `t1`.`slug` AS `t1_c3`,
       `t1`.`variant_slug` AS `t1_c4`, `t1`.`title` AS `t1_c5`,
       `t1`.`description` AS `t1_c6`, `t1`.`temporal_start` AS `t1_c7`,
       `t1`.`temporal_end` AS `t1_c8`
FROM `products` AS `t0`
LEFT JOIN `product_i18n` AS `t1`
     ON (`t0`.`id` = `t1`.`product_id`)
WHERE `t1`.`variant_slug` = 'test-product'
Ben Swinburne
  • 25,669
  • 10
  • 69
  • 108
  • I tried this method while posting my solution , But didnt worked for an ORM query , I don't know whether I missed some thing or not , Can you post with a query example , I am just asking for the sake of learning . – Aravind.HU Apr 23 '13 at 17:08
  • Updated my example for you – Ben Swinburne Apr 24 '13 at 12:10
  • It does work for ORM as well, the issue probably is that the query you would like to see is not the last query. – WanWizard Apr 27 '13 at 19:28
2

There are some means to achieve this , If you just want to print the complex query you have built , use get_query(); method of your ORM query class .

Below is the sample code which prints the query . 

$queryToExecute = Model_Article::query()
        ->select('author')
        ->where('date', '<', time())
        ->where('draft', '=', 1);


// This will print the query which is actually executed by your ORM 
echo $queryToExecute->get_query();

If you want to log your query and execute 

$queryString = $queryToExecute->get_query();
Log::info("Query that is executed {$queryString} ");

If you want to analyze the performance of your query , you should use DB profiler , by enabling in your application configuration .

Hope my above solution solves your problem

Aravind.HU
  • 9,194
  • 5
  • 38
  • 50
0

After execute query, you can use follow statement

echo \Database_Connection::instance(Model_Foo::$_connection)->last_query;

karisan
  • 1