How to get the raw SQL query generated by Eloquent

Introduction

When working with Laravel’s Eloquent ORM, the elegance and simplicity of active record patterns can sometimes obscure the underlying database interactions. For debugging, optimizing, or simply understanding what’s going on under the hood, developers often need to see the raw SQL queries that Eloquent generates. This article explores multiple methods to retrieve the raw SQL queries from Eloquent, progressing from basic examples to more advanced use-cases.

Enabling the Query Log

One of the simplest ways to see what SQL queries Laravel is executing is by enabling the query log. Laravel keeps a log of all queries run by the application which can be accessed via the DB facade.

use Illuminate\Support\Facades\DB;

DB::enableQueryLog();

// Perform some database operations...

$queries = DB::getQueryLog();
print_r($queries);

In the above code snippet, you enable the query log before executing database queries. After your operations, you can retrieve the log and print it out.

Using the toSql() Method

The toSql() method on a query builder instance returns the SQL for the query without running it against the database.

$users = User::where('active', 1)->toSql();

echo $users;

This will print out something akin to:

select * from `users` where `active` = 1

Binding Parameters

Seeing the query placeholders can be helpful, but sometimes you need the full query with bindings. Eloquent doesn’t provide a direct way to do this, but you can utilize the getBindings() method in conjunction with toSql():

$query = User::where('active', 1);
$sqlWithBindings = str_replace(array('%', '?'), array('%%', '%s'), $query->toSql());
$sqlWithBindings = vsprintf($sqlWithBindings, $query->getBindings());

echo $sqlWithBindings;

Be cautious when injecting bindings directly into your query string; it can make your application vulnerable if not handled correctly.

Using DB::listen()

You can also have Laravel listen for database queries and execute your own callback each time a query is executed:

DB::listen(function ($query) {
    var_dump($query->sql);
    var_dump($query->bindings);
    var_dump($query->time);
});

// Any query executed hereafter will trigger the above callback.

The callback receives a query object that contains the SQL string, the bindings, and the execution time. This method is particularly useful during development or debugging.

Inspecting Pagination Queries

Pagination is a common feature in applications, but it can lead to confusing queries due to the automatic adjustments made by Eloquent. To see what’s going on, you can log the queries or convert them to SQL:

$users = User::where('active', 1)->paginate(15);

// Using toSql() on a paginator instance won't work as expected.
// Instead, get the underlying query:
$query = $users->toBase();
$querySql = $query->toSql();

// You can then dump the bound query as before

Advanced Example

Extending Eloquent’s Builder class to add new functionalities is a powerful feature of Laravel. Here, I’ll provide an example where we extend the Builder class to add a custom debug method. This method will enable us to log the SQL query and its bindings for debugging purposes.

Step 1: Extend Eloquent’s Builder Class

First, create a custom Builder class that extends Laravel’s Builder class.

Create a new file App\Eloquents\CustomBuilder.php:

<?php

namespace App\Eloquents;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\Log;

class CustomBuilder extends Builder
{
    /**
     * Log the query SQL and bindings.
     *
     * @return $this
     */
    public function debugQuery()
    {
        $sql = $this->toSql();
        $bindings = $this->getBindings();

        Log::debug('SQL Query: ' . $sql, ['bindings' => $bindings]);

        return $this;
    }
}

Step 2: Extend Eloquent’s Model Class

Next, extend the base Eloquent model to use this custom Builder.

Create a new file App\Eloquents\CustomModel.php:

<?php

namespace App\Eloquents;

use Illuminate\Database\Eloquent\Model;

class CustomModel extends Model
{
    /**
     * Create a new Eloquent query builder for the model.
     *
     * @param  \Illuminate\Database\Query\Builder  $query
     * @return \App\Eloquents\CustomBuilder|static
     */
    public function newEloquentBuilder($query)
    {
        return new CustomBuilder($query);
    }
}

Step 3: Use the Custom Model

Now, use the CustomModel as the base model for your Eloquent models.

<?php

namespace App\Models;

use App\Eloquents\CustomModel;

class User extends CustomModel
{
    // Model content here
}

Step 4: Using the debugQuery Method

Now you can use the debugQuery method in your Eloquent queries.

use App\Models\User;

// Example usage
User::where('name', 'John')->debugQuery()->get();

This will log the SQL query and its bindings to the default log channel set in Laravel, which can be extremely helpful for debugging complex queries.

Conclusion

In this tutorial, we explored various ways to expose Eloquent’s underlying SQL statements, which can be incredibly useful for Laravel developers seeking transparency into the ORM’s internal mechanics or needing to troubleshoot complex database interactions.