Eloquent Advanced Subqueries

eloquent-subqueries

Eloquent also offers advanced subquery support, which allows you to pull information from related tables in a single query. For example, let’s imagine that we have a table of flight destinations and a table of flights to destinations. The flights table contains an arrived_at a column which indicates when the flight arrived at the destination.

Using the subquery functionality available to the select and addSelect methods, we can select all of the destinations and the name of the flight that most recently arrived at that destination using a single query:

use App\Destination;
use App\Flight;

return Destination::addSelect(['last_flight' => Flight::select('name')
    ->whereColumn('destination_id', 'destinations.id')
    ->orderBy('arrived_at', 'desc')
    ->limit(1)
])->get();

Subquery Ordering

In addition, the query builder’s orderBy the function supports subqueries. We may use this functionality to sort all destinations based on when the last flight arrived at that destination. Again, this may be done while executing a single query against the database:

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

Social Media

New From Animag

Get The Latest Updates

Subscribe To Our Weekly Newsletter

No spam, notifications only about new products, updates.