How to Get the Raw SQL Query from Laravel Query Builder

When working with Laravel's Eloquent ORM or Query Builder, you may often need to debug or inspect the raw SQL query being executed. Laravel provides several ways to retrieve the raw SQL query, including the query structure, bindings, and execution time. In this article, we’ll explore different methods to get the raw SQL query from the Query Builder.

---

Why Retrieve Raw SQL Queries?

Retrieving raw SQL queries is useful for:

1. Debugging: Inspect the exact query being executed.
2. Optimization: Analyze query performance and identify bottlenecks.
3. Learning: Understand how Laravel's Query Builder translates methods into SQL.
4. Logging: Log queries for auditing or monitoring purposes.

---

Methods to Get Raw SQL Queries

1. Using toSql()

The toSql() method returns the SQL query as a string without the bindings. This is useful for quickly checking the query structure.

Example:

$query = OrderItem::select('t_products.id as item_id')
    ->join('t_products', 'order_items.product_id_fk', '=', 't_products.id')
    ->where('t_products.is_raw_material', true);

$sql = $query->toSql();
dd($sql);

Output:

select "t_products"."id" as "item_id" from "order_items" inner join "t_products" on "order_items"."product_id_fk" = "t_products"."id" where "t_products"."is_raw_material" = ?

---

2. Using toSql() with Bindings

To include the bindings in the query, use the getBindings() method and manually replace the placeholders (?) with the actual values.

Example:

$query = OrderItem::select('t_products.id as item_id')
    ->join('t_products', 'order_items.product_id_fk', '=', 't_products.id')
    ->where('t_products.is_raw_material', true);

$sql = $query->toSql();
$bindings = $query->getBindings();

// Replace placeholders with bindings
$fullSql = vsprintf(str_replace('?', '%s', $sql), $bindings);

dd($fullSql);

Output:

select "t_products"."id" as "item_id" from "order_items" inner join "t_products" on "order_items"."product_id_fk" = "t_products"."id" where "t_products"."is_raw_material" = 1

---

3. Using DB::getQueryLog()

Enable query logging to retrieve the full query log, including bindings and execution time.

Example:

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

// Execute your query
OrderItem::select('t_products.id as item_id')
    ->join('t_products', 'order_items.product_id_fk', '=', 't_products.id')
    ->where('t_products.is_raw_material', true)
    ->get();

// Get the query log
$queries = DB::getQueryLog();
dd($queries);

Output:

[
    [
        "query" => "select `t_products`.`id` as `item_id` from `order_items` inner join `t_products` on `order_items`.`product_id_fk` = `t_products`.`id` where `t_products`.`is_raw_material` = ?",
        "bindings" => [1],
        "time" => 5.12,
    ]
]

---

4. Using dd() or dump() with Query Builder

Use the dd() or dump() helper functions to inspect the SQL and bindings directly.

Example:

$query = OrderItem::select('t_products.id as item_id')
    ->join('t_products', 'order_items.product_id_fk', '=', 't_products.id')
    ->where('t_products.is_raw_material', true);

dd($query->toSql(), $query->getBindings());

Output:

"select `t_products`.`id` as `item_id` from `order_items` inner join `t_products` on `order_items`.`product_id_fk` = `t_products`.`id` where `t_products`.`is_raw_material` = ?"

[1]

---

5. Using a Macro (Optional)

For convenience, you can create a macro to retrieve the full SQL query with bindings.

Step 1: Add the Macro to a Service Provider

Add this to a service provider (e.g., AppServiceProvider):

use Illuminate\Database\Query\Builder;

Builder::macro('toRawSql', function () {
    return vsprintf(str_replace('?', '%s', $this->toSql()), $this->getBindings());
});

Step 2: Use the Macro

Now you can use the toRawSql() method on any query:

$query = OrderItem::select('t_products.id as item_id')
    ->join('t_products', 'order_items.product_id_fk', '=', 't_products.id')
    ->where('t_products.is_raw_material', true);

dd($query->toRawSql());

Output:

select `t_products`.`id` as `item_id` from `order_items` inner join `t_products` on `order_items`.`product_id_fk` = `t_products`.`id` where `t_products`.`is_raw_material` = 1

---

Which Method Should You Use?

• toSql(): For a quick look at the query structure.
• toSql() with getBindings(): For the full query with bindings.
• DB::getQueryLog(): For detailed query logs, including execution time.
• Macro (toRawSql()): For convenience if you frequently need the full query.

---

Conclusion

Retrieving raw SQL queries in Laravel is a powerful debugging and optimization tool. Whether you use toSql(), DB::getQueryLog(), or a custom macro, these methods will help you inspect and analyze your queries effectively. By understanding how Laravel constructs SQL queries, you can write more efficient and maintainable code.