Laravel Last Executed Query In Plain SQL Statement For Debugging

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

Random Articles For You

Laravel Clear Cache Of Route, View, Config Command

Generate SSH Key with ssh-keygen In Linux / Unix

Manipulate HTML Using DOMDocument In PHP

Facebook Login With PHP Laravel Socialite

Create Gmail App Password For SMTP Mails

Factory States For Clean And Fluent Laravel Testing

Create Custom 404 Page In Laravel

Testing Laravel Emails With MailHog

Stripe Payment Integration With Laravel

Foreign Key Migrations Simplified In Laravel 7.x

Laravel Custom Maintenance Page

What Is Laravel Resourceful Controllers?

Responsive YouTube Videos

Accessors & Mutators In Laravel. Problem In Set Attribute While Saving/Updating Data & How To Resolve It

What Is Method Chaining In PHP?