How to add related aggregate data in Laravel Eloquent?

One Metric model has many Measurements.

I've set up the relationship so I can do things like:

$metrics = Metric::with('measurements' => function($query) {
   return $query->where('timestamp', '>=', '20190731');
});

This works to fetch all the measurements from 31 July 2019 for the metrics.

But what I want to achieve now is to fetch aggregated measurement data, i.e.

SELECT metrics.*, stats.*
  FROM metrics
 LEFT JOIN (
            SELECT DATE_FORMAT(m.timestamp, '%Y%M') as period,
                   MIN(value) AS min, MAX(value) AS max
            FROM measurements m
            WHERE m.metric_id = metrics.id
            GROUP BY DATE_FORMAT(m.timestamp, '%Y%m')
      ) AS stats

Is there a way to achieve that in Eloquent/Laravel query builder? (Laravel 5.8)

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/313LbQT
via IFTTT