Eager loading in Laravel query builder

Eager loading in Laravel query builder

Posted on:August 24, 2019 at 10:00 AM

Sometimes you need to write some query in laravel by using Query Builder, at the same time you might need to use Eager loading the relationship. But unfortunately, eager loading and query builder does not fit together. So, in this scenario, how to do that? Today, I will show you a trick that might help you to depends on query builder less where you can easily implement eager loading.

Table of Contents

Open Table of Contents

Scenario

Imagine I have a table called events where I need to fetch ccFullAddress column then group by ccFullAddress where I need to count all the ccFullAddress as total and finally order by total desc. At the same time, in the view side, I will show the country name from the countries table based on ccFullAddress data.

There are a few options to make it succeed. Maybe I can use query builder and create a global helper function to get the country name by passing a parameter in this function. Here we have come out with a solution.

return DB::table('events')
            ->select('ccFullAddress', DB::raw('count(ccFullAddress) as total'))
            ->groupBy('ccFullAddress')
            ->orderBy('total', 'desc')
            ->get();

In the view side-

<a href="" target="_blank">
	{{ getCountryNameById($event->ccFullAddress)->countryname }}
</a>

The above code will solve our issue however, it will create N+1 issue.

Alternatively, we can use eager loading that load on eloquent. But we cannot use that since we need to count that.

So, how to solve this issue? Well, I tried to merge both together and come out with this solution-

Event::with('country')
            ->select('ccFullAddress', DB::raw('count(ccFullAddress) as total'))
            ->groupBy('ccFullAddress')
            ->orderBy('total', 'desc')
            ->get();

Here I use eloquent then I try to use DB facade on that. In the view part, I try this way-

<a href="">{{ $event->country->countryname }}</a>

And of course, I set a relationship between countries and events tables. So, we can easily fetch data from the country table.

Hope it will help you.

laravelquery-builder