Conditional queries in Laravel 4

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.