Eloquent Subquery Enhancements - Laravel

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.

• “Select” subqueries

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();

Notice how we’re using Eloquent to generate the subquery here. This makes for a nice, expressive syntax. 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();

• “Order by” subqueries

use subqueries in the query builder’s orderBy() method. Continuing our example above, we can use this to sort the destinations based on when the last flight arrived at that destination

return Destination::orderByDesc(
    Flight::select('arrived_at')
        ->whereColumn('destination_id', 'destinations.id')
        ->orderBy('arrived_at', 'desc')
        ->limit(1)
)->get();

As with selects, you can also use the query builder directly to create the subquery. For example, maybe you want to order users based on their last login date

return User::orderBy(function ($query) {
    $query->select('created_at')
        ->from('logins')
        ->whereColumn('user_id', 'users.id')
        ->latest()
        ->limit(1);
})->get();

• “From” subqueries

For example, maybe you want to calculate the average total donations made by users in your application. However, in SQL it’s not possible to nest aggregate functions AVG(SUM(amount))
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');

I tried to present some basic points, but to go deeper, visit the source.
I hope you enjoyed with me and I adore you who search for everything new.