Unmasking Eloquent: Getting Full SQL for Eager Loaded Queries with toSql()

As developers working with Laravel and Eloquent, one of the most powerful features we leverage is Eloquent’s ability to handle complex database interactions through expressive syntax. One common task is debugging exactly what SQL statements are being generated by our queries, especially when dealing with eager loading using the with() clause.

However, as you've discovered, simply calling $query->toSql() on a query that includes relationships might only return the primary selection, leaving out the necessary subqueries generated by the eager loading mechanism. This can be frustrating when trying to trace exactly how your application is interacting with the database.

This post will dive into why this happens and provide the correct developer-focused strategies for inspecting all SQL statements generated by an Eloquent query, including those resulting from with() clauses.

---

The Mystery of Missing Eager Load SQL

The issue stems from how the underlying Query Builder handles eager loading. When you use with('relation'), Eloquent doesn't simply add a JOIN to retrieve related data in the main result set (which is what toSql() focuses on). Instead, for performance reasons, Eloquent executes separate queries to fetch the related data after the initial query runs—this is known as eager loading.

Let’s look at your example scenario:

class Child extends EloquentVersioned {
    public function childRequests()
    {
        return $this->hasMany('ChildRequest');
    }   
}

$childQuery = Child::orderBy('last_name')
                   ->orderBy('first_name')
                   ->with('childRequests');

// Result of toSql(): Only the main table query is returned.
return $childQuery->toSql(); 

As you correctly observed, $childQuery->toSql() only returns: select children.* from children order by last_name asc, first_name asc. It omits the necessary queries for ChildRequest because those are executed separately during model hydration, not as part of the initial SQL string.

How to Inspect All Generated SQL Statements

Since toSql() is designed to return the raw SQL for the primary operation, we need alternative methods to capture the full set of statements generated by an eager-loaded query. The solution involves leveraging the underlying database connection or using Eloquent’s debugging tools.

Method 1: Using toSql() and Subsequent Execution (The Practical Approach)

If your ultimate goal is to see all queries that Laravel executes when retrieving the data, you must execute the query within a context where the full execution path is visible. While not directly showing all eager-load statements in one string, this method confirms the behavior of the entire operation.

Method 2: Leveraging the Database Query Log (The Deep Dive)

For truly debugging complex interactions, especially involving eager loading, relying on Laravel’s built-in query logging mechanism is often the most robust approach. This log captures every query executed during the request lifecycle. While this isn't strictly tied to a single $query->toSql() call, it provides the complete picture of what the database received.

You can enable logging via your config/database.php file or by using the DB::getQueryLog() method directly within your application logic:

use Illuminate\Support\Facades\DB;

// Enable query logging for inspection
DB::enableQueryLog(); 

$childQuery = Child::orderBy('last_name')
                   ->with('childRequests');

$childQuery->toSql(); // Still gets the main SQL

// Execute the query to trigger the logs
$childQuery->get(); 

// Retrieve all logged queries
$queries = DB::getQueryLog();

// The $queries array will now contain both the main query and the eager-loaded child requests.
dd($queries); 

By using DB::getQueryLog(), you gain access to an array of all SQL statements executed during that operation. This is a powerful technique for understanding exactly how Eloquent translates your model relationships into database calls, which aligns perfectly with the principles of clean data access demonstrated by resources like Laravel Company.

Conclusion

When debugging Eloquent queries involving eager loading, remember that toSql() is context-specific—it shows the SQL for the immediate operation. To inspect the entire chain of database interactions, including those generated by with() clauses, shift your focus from string manipulation to execution logging. By employing methods like DB::getQueryLog(), you gain complete transparency into the query flow, allowing you to master complex data retrieval patterns efficiently.