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.