DEV Community

Sospeter Mong'are
Sospeter Mong'are

Posted on

How to Get the Raw SQL Query from Laravel Query Builder

#sql #laravel #programming #beginners

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);
Enter fullscreen mode Exit fullscreen mode

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" = ?
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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,
    ]
]
Enter fullscreen mode Exit fullscreen mode

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());
Enter fullscreen mode Exit fullscreen mode

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]
Enter fullscreen mode Exit fullscreen mode

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());
});
Enter fullscreen mode Exit fullscreen mode

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());
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)