Laravel Eloquent: When To Constrain Eager Load or To Use Relationship Existence — Make Search Query

This writing is not me trying to say which is right or wrong. Besides sharing my experience, this story is also for my reminder for other similar cases in the future.

Photo by Ilya Pavlov on Unsplash

So anyone who has worked with Laravel must know that one of the best things about Eloquent is “eager loading”. It really helps a lot with SQL queries and really simple to use on the blade files or even as a REST API output, also it helps with the resource usage (if you notice it, especially if you call it through AJAX). Although it really simple to use but there will be times when you need to custom the queries or make a more advanced query. For say, like what I have learned in a hard way (it took me almost 3 days to figure things out, a reminder for myself as well). So I needed a kind of search query on a database that has a relationship between users, profiles, and photos tables. The parameters for the query are also coming from a form which works as a kind of filter. To give you a better idea of what I was dealing with, take a look at below DB scheme.

DB scheme

So, I needed to create a query where it will filter the results based on the relationship values. I won’t be discussing making the relationship models here, I’m sure anyone interested in this story already knows that and already gets a result using eager loading. The basic query is like this:

$return = User::where(‘status’, ‘active’)->with(‘profile’, ‘photos’)->get();

So the next thing is to create a custom filter based on the input parameters. To achieve that I m using a method I like to call “chopped syntax”.

$return = User::where(‘status’, ‘active’);
if($request->has(‘filterByAge’){
    $return = $return->where(‘age’, ‘=’, $request->filterByAge);
}
$return = $return->with([‘profile’, ‘photos’]);
$return = $return->get();

Now, the problem is there are several parameters that will be querying the child tables, and on the profiles table, some columns are JSON type. So the first thing that came in my mind is to create a constrain for the eager load function. Something like this:

$return = $return->with([‘profile’ => function($query){…}, ‘photos’]);
$return = $return->has(‘profile’)->has(‘photos’, ‘>’, 0);
$return = $return->get();

This is good actually, but if there is a row that doesn’t meet the first condition but has values for the second condition instead of throwing away that row it still includes the rows with NULL relation value (in my case it’s the “profile” parameter). This is of course making the query result hard to process, especially if I want to use pagination. My first solution was to rerun the query based on the first query returns that I filtered using Collections and create manual pagination. I really not recommend this method because it really takes up your resources and often kills the MySQL service although it was working.

The solution is actually pretty simple, but I had to find it the hard way. So instead of constraining your eager loads. It’s so much better to do the custom queries on “Relationship Existence”. So the codes would look like this:

use Illuminate\Database\Eloquent\Builder;
public function searchByParams(Request $request)
{
   $return = User::where(‘status’, ‘active’);
   if($request->has(‘filterByAge’){
      $return = $return->where(‘age’, ‘<=’, $request->filterByAge);
   }
   $return = $return->with(‘profile’, ‘photos’)
   $return = $return->whereHas('profile', function(Builder $query) use($request){
       if($request->has('height'){
          $query = $query->where('basic->height', '<=', $request->height);
       } 
       // you can add more filters here with the condition you need.
       // as long as you don't forget to return the $query.
       return $query;
    });
    $return = $return->paginate(25);
    return response()->json($return);
}

I can’t find any official documentation to justify the codes above, but it’s working as expected. It will eliminate the rows that don’t meet the conditional from filter parameters instead of returning rows with NULL “relations” value.

As I mentioned up front, I am not saying that this is the best method. This is more to share my experiences and a good place for me to recall this method in later similar situations. If it’s useful for you, I’m happy with it. If you know another better method, please kindly share it with me.

Thank you for reading my first story in Medium. I would love to share more in the future (if I have the time).