By Harris Raftopoulos in laravel — Jun 13, 2024

Optimizing database queries with `whereRaw()` in Laravel

Laravel devs, here's a gem for you: 💎

Optimize your database queries using whereRaw() for complex conditions. This feature allows you to write raw SQL queries, providing more flexibility and potentially improving performance. In this blog post, we'll explore how to use whereRaw() and provide a real-life example to demonstrate its benefits.

Why Use `whereRaw()`?

Complex Conditions: Handle more complex conditions that are difficult to express using Eloquent's query builder.
Performance: Optimize performance by leveraging the full power of SQL for specific use cases.
Flexibility: Write raw SQL queries directly within your Eloquent statements.

Step-by-Step Implementation

Let's walk through the process of setting up and using whereRaw() in a Laravel application.

Step 1: Setting Up the Model

Ensure you have a model to work with. In this example, we'll use an Order model.

// app/Models/Order.php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Order extends Model
{
    // Model configurations if needed
}

Step 2: Creating the Controller Method

Create a controller method that uses whereRaw() to filter orders based on complex conditions.

// app/Http/Controllers/OrderController.php

namespace App\Http\Controllers;

use App\Models\Order;
use Illuminate\Http\Request;

class OrderController extends Controller
{
    public function index()
    {
        $orders = Order::whereRaw('price > IF(state = "TX", ?, 100)', [200])->get();

        return view('orders.index', ['orders' => $orders]);
    }
}

Step 3: Setting Up the Route

Define a route that points to the controller method.

// routes/web.php

use App\Http\Controllers\OrderController;

Route::get('/orders', [OrderController::class, 'index']);

Step 4: Creating the View

Create a view to display the filtered orders.

<!-- resources/views/orders/index.blade.php -->

<!DOCTYPE html>
<html>
<head>
    <title>Orders</title>
</head>
<body>
    <h1>Orders</h1>
    <ul>
        @foreach ($orders as $order)
            <li>Order ID: {{ $order->id }}, Price: {{ $order->price }}, State: {{ $order->state }}</li>
        @endforeach
    </ul>
</body>
</html>

Real-Life Example: Filtering Orders

In a real-life scenario, you might need to filter orders based on complex conditions that vary depending on certain fields. Using whereRaw(), you can write raw SQL to handle these cases.

Creating Dummy Data

Let's generate some dummy data to work with.

php artisan tinker

// Inside Tinker
Order::create(['price' => 150, 'state' => 'TX']);
Order::create(['price' => 250, 'state' => 'TX']);
Order::create(['price' => 80, 'state' => 'CA']);
Order::create(['price' => 120, 'state' => 'CA']);

Viewing the Orders

Access the /orders route in your browser to see the filtered list of orders.

Conclusion

Using whereRaw() in Laravel allows you to handle complex SQL conditions and optimize your database queries. By following the steps outlined in this blog post, you can leverage the full power of SQL within your Laravel applications, improving both flexibility and performance.

Found this helpful?

If this guide was helpful to you, subscribe to my daily newsletter and give me a follow on X/Twitter. It helps a lot!

Why Use whereRaw()?