How to Write Complex WHERE in Laravel Eloquent: 4 Ways with Examples

How to Write Complex WHERE in Laravel Eloquent: 4 Ways with Examples

Imagine: you are building an online store on Laravel. You need to find all products that cost between 1000 and 5000 rubles, are in stock, but not from the "Sale" category, and were created after January 1, 2024. How do you do this in one query without killing performance?

This question is one of the most popular on Stack Overflow (thousands of views!). And for good reason: the ability to build complex WHERE conditions in Laravel Eloquent distinguishes a beginner from a production developer.

In this article, I will show you 4 proven ways to create multiple WHERE conditions. You will learn when to use arrays and when to use closures, how to group conditions, and how to prevent SQL injections. Let's go!

1. Basic Way: Array of Conditions (for Simple AND)

The simplest case is when all conditions are joined with AND. Laravel allows you to pass a regular array to where():

<?php\n\n// We want to find active users from Russia, created in 2024\n$users = User::where([\n    ['status', '=', 'active'],\n    ['country', '=', 'Russia'],\n    ['created_at', '>=', '2024-01-01'],\n])->get();\n\n// SQL: SELECT * FROM users WHERE status = 'active' AND country = 'Russia' AND created_at >= '2024-01-01'

When to use: when you have 2-5 simple conditions with the = or > operator. The code is readable but not flexible.

Downside: if you need to mix AND and OR, an array won't work.

2. Method Chaining with where() (for Dynamic Conditions)

This is the most popular method among developers. You simply call where() multiple times in a row:

<?php\n\n$products = Product::where('price', '>', 1000)\n    ->where('price', '<', 5000)\n    ->where('in_stock', true)\n    ->where('category_id', '!=', 5)\n    ->get();\n\n// SQL: ... WHERE price > 1000 AND price < 5000 AND in_stock = 1 AND category_id <> 5

Trick: you can combine it with other conditions:

<?php\n\n// "OR" condition inside the chain\n$orders = Order::where('status', 'paid')\n    ->orWhere('status', 'shipped')\n    ->where('total', '>', 100)  // Note: this condition applies to the previous orWhere!\n    ->get();\n\n// SQL: ... WHERE status = 'paid' OR (status = 'shipped' AND total > 100)

⚠️ Important! In the last example, where total > 100 applies only to status = 'shipped'. If you need both statuses to have total > 100 — read the next section.

3. Grouping Conditions via Closures (AND + OR Together)

Here we get to the most powerful tool. When you need to create a group of conditions in parentheses — use a closure inside where():

<?php\n\n// Task: find orders with status "paid" or "shipped",\n// but total must be > 100 in both cases\n$orders = Order::where(function($query) {\n        $query->where('status', 'paid')\n              ->orWhere('status', 'shipped');\n    })\n    ->where('total', '>', 100)\n    ->get();\n\n// SQL: ... WHERE (status = 'paid' OR status = 'shipped') AND total > 100

Now the conditions are grouped correctly! You can nest closures inside each other as many times as you want:

<?php\n\n// Complex filter for analytics\n$data = Sale::where('date', '>=', '2024-01-01')\n    ->where(function($q) {\n        $q->where('amount', '>', 10000)\n          ->orWhere('is_vip', true);\n    })\n    ->where(function($q) {\n        $q->