How to Use Nested Queries in Laravel v5.2
In this post I will go over how to use nested queries in Laravel v5.2 and also cover some different situations in which they come in handy. Before we get into that I just want it to be clear that I know that Laravel is currently at version 8 right now but if you are working with legacy Laravel code and your upgrade path is long and slow, I figured this might come in handy. Enjoy.
What are nested queries?
In Laravel v5.2, several query methods such as where(), whereIn(), orWhereIn(), orWhere(), etc. accept a function as the 2nd parameter. What does this mean for you? Using this function option you can nest multiple sets of queries and form very complex mySQL statements with minimal code.
A nested query example
Let’s say you want to write a query that goes something like this:
Return “col3” where “col1” equals 1 and where “col2” equals 2, 3 or 4
In this case the regular “OR” statement “->orWhere()” would not work by itself because you would end up with “where col1 = 1 OR col2 = 1 OR col2=2…” which is not the desired outcome. We need both col1 and col2 to return true in the query but col2 has several different values it can be in order to return true. Herein lies the complication and one reason to use a nested query.
Instead of a regular OR statement we can write 2 where statements and assign the result of a nested query as one of the values that the where checks like so:
$query->select('col3')
->where('col1', 1)
->where(function($q){
$q->where('col2', 2)
->orWhere('col2', 3)
->orWhere('col2', 4)
});The above statement will result in col3 returning if col1 equals 1 and col2 equals 1, 2, 3 or 4. This is a very basic example but by using nested queries you can mix and match for different situations and create some very complex and powerful queries.
Another nested query example
Let’s say you want to write a query that goes something like this:
Return “col3” where “col1” equals 1 and where either “col2”, “col3” or “col4” are in this array [1,2,3,4]
Again in this scenario the regular “->orWhereIn()” method would not work because you instead of checking one column to be in an array, you are checking for one of 3 different columns to be in the array in order to return true. To get the right result, we will use the regular where() statement with a nested “OR” query set containing the queries that check the different columns against the array:
$checkArray = [1,2,3,4];
$query->select('col3')
->where('col1', 1)
->where(function($q) use ($checkArray){
$q->whereIn('col2', $checkArray)
->orWhereIn('col3', $checkArray)
->orWhere('col4', $checkArray)
});Wrapping up
I hope this post has helped shed some light on the power of nested queries and how you can use them in your own code. Personally I have found them to be a life-saver in many situations where I had multiple “AND” statements that contained multiple “OR” statements due to conditional values, etc. that I had to work with.
If you are interested in more Laravel-based content visit the Laravel category page.
Thank you for stopping by and God bless you.