🎁 Only Today 20% Discount Code "ONE-YEAR-LCA" For Laravel Core Adventures Get The Course →

Conditional queries in Laravel 4

#laravel

I'm too old for this sh**.

This post has already become quite old, and a lot has changed in that field since I wrote it. Examples I provide might not work anymore or are not suitable for how we work today. Please be aware of that while reading it.

Queries in Laravel are damn easy. With Eloquent and the Laravel Query Builder you can build your queries directly in PHP without writing any SQL yourself. With your applications growing your requirements will grow too. In some cases you will need to chain your sql parts based on conditions. This article will show you how to work with conditional queries in Laravel.

The scenario

Recently I was working on a Laravel project where I was implementing a search. It was a product search with a lot of different options to filter results. On the backend I got all the necessary information to build the database query. Of course with all these different options the query needs to be build different for every search. Let's take a look at a simple example.

$category = 'food';
$color = 'red';

$searchResults = DB::table('awesome_products')
	->where('category', $category)
	->where('color', $color)
	->get();

We are using the Laravel query builder to select all products from the category "food" with the color "red". Of course in a real application this would look different, but as I mentioned this is a simple example to show the scenario. But what If only the category is defined and not the color? A first attempt would probably look like this.

if(isset($category) && isset($color)) {

	$searchResults = DB::table('awesome_products')
		->where('category', $category)
		->where('color', $color)
		->get();

} eleseif(isset($category)) {

	$searchResults = DB::table('awesome_products')
		->where('category', $category)
		->get();

}

If both variables are given we take both into the query. If only the category is given we remove the color. This is not the prettiest way, but it would work for this case. For me this was not an option because I had dozens of options and it would make no sense to write the whole query again for every option.

Way 1 - Advanced where clauses

So what we need are conditions. If a filter is given we need to add it to the query without writing it all again. Fortunately Laravel can help us on that in two ways. For our simple example let's assume we have some more options: category, color, size, weight and hotDeal

// Way 1: Advanced where clauses

$searchResults = DB::table('awesome_products')
	->where(function($query) use ($category, $color, $size, $weight, $hotDeal) {
		if($category)
			$query->where('category', $category);

		if($color)
			$query->where('color', $color);

		if($size)
			$query->where('size', $size);

		if($weight)
			$query->where('weight', $weight);

		if($hotDeal)
			$query->where('hotDeal', $hotDeal);
	})
	->get();

With advanced where clauses we can pass variables to an anonymous function in which we can build the where clauses based on conditions. Isn't that great? We only have to write the repeating parts (in this case the beginning and end) once. This way we have to write much less and the code gets more simple.

Way 2 - Splitting the whole query

Way 1 is awesome but has its limits: It works just for where clauses! Let's assume we need another condition including offset and limit. These options can't be set in where clauses. Of course Laravel can help us here too. Let's take a look.

// Way 2: Splitting the whole query

$query = DB::table('awesome_products');

if($category)
	$query->where('category', $category);

if($color)
	$query->where('color', $color);

if($size)
	$query->where('size', $size);

if($weight)
	$query->where('weight', $weight);

if($hotDeal)
	$query->where('hotDeal', $hotDeal);

if($offset && $limit)
	$query->skip($offset)
		->take($limit);

$searchResults = $query->get();

Did you know that you can split the whole query in its parts? I didn't know that for too long! But these days are over. This way we can write every part of the query based on conditions and that was exactly what I was looking for my project.

Conclusion

Laravel has built in a lot of helpers to make your work as easy as possible. Obviously it can't take care of all your application's requirements by itself, but you always have the possibility to enhance the framework for you needs. This is what makes Laravel so damn powerful for beginners and advanced users.

Did you enjoy this post?

Sign up for my newsletter to stay up to date.

You will receive monthly updates on my latest articles and products. I do care about the protection of your data. Read my Privacy Policy.