2 years ago

I found this great old article https://laravel-news.com/eloquent-subquery-enhancements from 2019 and it contains some very important Eloquent Subquery that helps you in dealing with databases and makes you more flexible with your future ideas. Using the new subquery select functionality in Laravel 6.0, we can select all of the destinations and the name of the flight that most recently arrived at that destination using a single query return Destination::addSelect(['last_flight' => Flight::select('name') ->whereColumn('destination_id', 'destinations.id') ->orderBy('arrived_at', 'desc') ->limit(1)])->get(); Enter fullscreen mode Notice how we’re using Eloquent to generate the subquery here.

That said, you can also do this using the query builde return Destination::addSelect(['last_flight' => function ($query) { $query->select('name') ->from('flights') ->whereColumn('destination_id', 'destinations.id') ->orderBy('arrived_at', 'desc') ->limit(1); }])->get(); Enter fullscreen mode

Instead, we can use a from subquery to calculate this return DB::table(function ($query) { $query->selectRaw('sum(amount) as total') ->from('donations') ->groupBy('user_id'); }, 'donations')->avg('total'); Enter fullscreen mode