Good content takes time and effort to come up with.

Please consider supporting us by just disabling your AD BLOCKER and reloading this page again.







https://stackcoder.in/Laravel Last Executed Query In Plain SQL Statement For Debugging


Laravel Last Executed Query In Plain SQL Statement For Debugging


LARAVEL DATABASE PHP DEBUG LARAVEL
07th June 2020 4 mins read
Share Article     Share On WhatsApp   Buy Me Coffee | StackCoder Buy Coffee


In this article, I will show you 2 ways to get the executed queries in plain SQL so that you can debug faster in Laravel.


We will cover


  1. toSql To Get Query
  2. Query Log To Get Query Depth Details

Prerequisites


Basic knowledge of Laravel queries running.


1) toSql To Get Query


First lets see how to use to toSql function to get the eloquent query in plain SQL format


Think your running the following query and want to get the plain sql


$posts = Post::with(['user', 'tags'])
        ->published()
        ->orderByDesc('published_at')
        ->get();


To get the plain SQL format of the above query use toSql() in the end instead of get() as shown below


$posts = Post::with(['user', 'tags'])
            ->published()
            ->orderByDesc('published_at')
            ->toSql();

echo '<pre>';
print_r($posts);


The output of the above eloquent query will be as follows


select * from `posts` where `published_at` <= ? and `posts`.`deleted_at` is null order by `published_at` desc

2) Query Log To Get Depth Details


Problem with Option 1


By using toSql() it will only show the current query executing and does not show the query it ran for with(['user', 'tags']) and doesn't show what parameters its using. And in a function if your running 4 to 5 query then you have to manually check each and every query.


Solution


DB::enableQueryLog(); & DB::getQueryLog()


By using the above functions we can resolve the problems faced with toSql() and other problems as explained above


/** Enable Query To Log All The Query Benith It */
\DB::enableQueryLog();

$posts = Post::with(['user', 'tags'])
    ->published()
    ->orderByDesc('published_at')
    ->get();
    
echo '<pre>';
/** With the help of the following function will tell to display all the query logged and stop logging further */
print_r(\DB::getQueryLog());


DB::enableQueryLog() - Use this line of code where you want to start logging the queries

DB::getQueryLog() - Use this line of code where you want to stop the logging of query and dump the output of the logs to the screen for debugging.


By running the above eloquent query it will show all the 3 queries ran to fetch the post details as below


Output

Array
(
  [0] => Array
    (
      [query] => select * from `posts` where `published_at` <= ? and `posts`.`deleted_at` is null order by `published_at` desc
      [bindings] => Array
        (
          [0] => 2020-06-08 16:10:48
        )

      [time] => 22.08
    )

  [1] => Array
    (
      [query] => select * from `users` where `users`.`id` in (1)
      [bindings] => Array
        (
        )

      [time] => 3.78
    )

  [2] => Array
    (
      [query] => select `tags`.*, `posts_tags`.`post_id` as `pivot_post_id`, `posts_tags`.`tag_id` as `pivot_tag_id` from `tags` inner join `posts_tags` on `tags`.`id` = `posts_tags`.`tag_id` where `posts_tags`.`post_id` in (?, ?, ?, ?) and `tags`.`deleted_at` is null
      [bindings] => Array
        (
          [0] => 71f0495d-6dc3-49c8-bf40-353c2be37be3
          [1] => 91bdcb4d-e43c-4c5b-856c-978d22f139bc
          [2] => 9a2a13ac-f5a5-406e-9a9b-7ab80aa1a6fb
          [3] => d49c93b5-75b0-41ab-ac79-4324bc116b96
        )

      [time] => 8.29
    )
)


Conclusion


Hope this was helpful. Kindly share with your friends :)


WHATS NEXT?


Firebase Cloud Messaging (FCM) Browser Web Push Notifications Using Javascript And PHP

Upload File From Frontend Server {GuzzleHttp} To REST API Server In PHP {Laravel}

Simple Way To Create Resourceful API Controller In Laravel

Move Uploaded Files From Local Computer Or Server To Amazon S3 Bucket In PHP

Why And How To Use PHP PDO With CRUD Examples

What Is Method Chaining In PHP?

Send Email In PHP With PHPMailer

How To Upload Multiple Files In PHP?

Proper Way To Validate MIME Type Of Files While Handling File Uploads In PHP


Happy Coding :)








Author Image
AUTHOR

Channaveer Hakari

I am a full-stack developer working at WifiDabba India Pvt Ltd. I started this blog so that I can share my knowledge and enhance my skills with constant learning.

Never stop learning. If you stop learning, you stop growing