Laravel Where Clause with Function Query Example

Sometimes, it is hard to create complex query in Laravel. For that in Laravel, we can use a function query with the where or  orWhere clause to perform complex queries. Below are given some example of how to use a function query with the where or  orWhere clause:

Example scenario 1:

Let’s say, you have a Product model. This model has two special properties such as: total_views and selling_price. So you want to fetch all the products which are views more than 5000 and also selling price is more than 10000.

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

class ProductController extends Controller
{
    public function index(Request $request)
    {
        $popular_products= Product::select("*")
            ->where("is_available", 1)
            ->where(function($query) {
                $query->where('total_views', '>', 5000)
                    ->orWhere('selling_price', '>', 10000);
            })
            ->get();

        dd($popular_products);
    }
}

Raw query for the above eloquent query is given below:

SELECT * FROM products WHERE is_available = 1 AND (total_views = > 5000 OR selling_price> 10000);

Example scenario 2:

orWhere with function:

In Laravel, the orWhere method is commonly used to add an OR condition to a query. If we want to use a function within the orWhere clause, we can pass a closure function to it. Here’s an example of how we can use orWhere with a function:

Let’s say, you have a User model. This model has some properties such as: name, age, gender. So you want to fetch all the users whose name is Max or age is more than 25 or gender is male.

$users = DB::table('users')
    ->where('name', '=', 'Max')
    ->orWhere(function ($query) {
        $query->where('age', '>', 25)
              ->orWhere('gender', '=', 'female');
    })
    ->get();

This example will produce the following SQL:  

select * from users where name = 'Max' or (age > 25 or gender = 'female');

That means orWhere method allows us to create complex queries with “or” conditions while properly grouping the conditions to achieve the desired behavior.

Check Out More Resources:

Articles:
Website: https://laravelplug.com/
YouTube Channel: https://www.youtube.com/channel/UCnTxncHQcBSNs4RCuhWgF-A?sub_confirmation=1
WordPress Playlist: https://www.youtube.com/watch?v=8VvXzFAFwQU&list=PLVoVZqAh8dTLHZ51egvEU7dsOrRCSIMWx
Tools Playlist: https://www.youtube.com/watch?v=gTQoUn3BQkM&list=PLVoVZqAh8dTK-DWHBGWOEEvzvPQrgFne-

WordPress Tutorials: https://laravelplug.com/category/wordpress/
Laravel Tutorials: https://laravelplug.com/category/laravel/
PHP Tutorials: https://laravelplug.com/category/php/
SQL Tutorials: https://laravelplug.com/category/sql/
Various Tips: https://laravelplug.com/category/tips/
Useful Tools: https://laravelplug.com/category/tools/

Socials:

Twitter: https://twitter.com/LaravelPlug
Pinterest: https://www.pinterest.com/LaravelPlugCom/
Facebook: https://www.facebook.com/groups/1020759861842360
Mail: info@laravelplug.com

#Laravel #sql #mysql

That’s All. Feel free to knock me. Thanks.