Extract summary statistics from a collection

Hello!

I'm relatively new to Laravel and I'm a bit stuck on the following problem I'm facing.

Context

I have defined several models in a database. To simplify, we can consider that the main model is a Flight::class, which relates to the following models (all of them eagerly loaded): a mandatory departure airport (Airport::class), a mandatory arrival airport (Airport::class), an optional airline (Airline::class) and an optional review (Review::class). Each Airport::class instance has a country property defined as a string in the database.

Each user then has a collection of flights that belong to them, that I extract from the database in the following way:

$userFlights = $user->flights()->orderBy("depDatetime")->get()

The problem

I would like to display to the user statistics regarding their flights. In particular, I´d like to do the following:

1. Find a way to know which distinct airlines the user used, how many times each airline was used, the total distance traveled by each airline and the mean note given to all the flights which belong to each airline.

$userAirlines = $userFlights->filter(function ($flight) {
    return !is_null($flight["airline"]);
})->groupBy("airline")->map(function ($group) {
    $meanNote = $group
        ->reject(fn ($flight) => $flight["review"]["airlineNote"] == 0)
        ->map(function ($flight) {
            return (float) $flight["review"]["airlineNote"];
        })
        ->avg();

    return [
        "airline" => $group->first()["airline"],
        "count" => $group->count(),
        "distance" => $group->sum("distance"),
        "meanNote" => round($meanNote, 1),
    ];
});					

Is this a reasonable way to approach the problem?

1. Now I would like to do the same for each airport. The problem here is that I would need to group by both the departure and arrival airports at once, as I'd like to gather the statistics from both instead of counting them separately. I'm not sure how to proceed with this. I tried to do the following:

$userAirports = $userFlights->pluck("depAirport")->merge($userFlights->pluck("arrAirport"))
    ->groupBy("id")->map(function ($group) {
        return [
            "airport" => $group->first(),
            "count" => count($group),
            "distance" => $group->sum("distance"),
        ];
    });

This seems to be working, but I don´t know how to include the mean note given to each airport in this method as I don´t have access to the Flight::class anymore in the map() function. Each user can have lots of flights, so I don´t want to do a query in each iteration of the map() function.

1. I would finally also like to know which routes have been used by the user in a similar way (a route being defined as a combination of departure and arrival airport).

$userRoutes = $userFlights->groupBy(function ($flight) {
    return $flight->depAirportId . "-" . $flight->arrAirportId;
})->map(function ($group) {
    return [
        "depAirport" => $group->first()->depAirport,
        "arrAirport" => $group->first()->arrAirport,
        "count" => $group->count(),
        "distance" => $group->sum("distance"),
    ];
});

Is this a reasonable way to approach the problem? I also have the same problem with the computation of the mean review note given though.

Thanks a lot for any help!