On a personal project, I have recently been working through showing a chart with total monthly views for movies and TV episodes over the last 12 months.

When implementing the process of calculating the total views, I came up with a few options in getting the data from the backend to the chart.

TLDR: See Option 3

Option 1 - Database

First impulse would be to use group by on your database relationship and select a custom set of columns. You can use the count() function get you the necessary total views and the date_format() to generate labels for each month.

/**
 * Get the views for the movie.
 */
public function views()
{
    $lastYear = Carbon::now()
        ->addMonth()
        ->startOfMonth()
        ->subYear();

    return $this->hasMany(MovieView::class)
        ->where('created_at', '>=', $lastYear)
        ->selectRaw(
            'movie_id, '.
            'COUNT(movie_id) as views, '.
            'DATE_FORMAT(created_at, \'%m/%y\') as label, '.
            'YEAR(created_at) as year, '.
            'MONTH(created_at) as month'
        )
        ->groupBy('year', 'month')
        ->orderBy('year', 'desc')
        ->orderBy('month', 'desc');
}

This method obviously has downsides:

The grouped columns are set as though they are attributes of a MovieView model. This is not an ideal option since your models should always stay clean.

You do not have any representation of past months with zero viewings.

Option 2 - Collections

You could move back to loading the actual activity records from the database. Then you can create an additional method on the model to generate a collection of all past 12 month totals.

I also brought in the League's Period package because it allows for ease in working with timespans, and solves the problem of creating those pesky zero total months.

use League\Period\Period;
// ...
/**
 * Get the views for the movie.
 */
public function views()
{
    $lastYear = Carbon::now()
        ->addMonth()
        ->startOfMonth()
        ->subYear();

    return $this->hasMany(MovieView::class)
        ->where('created_at', '>=', $lastYear);
}

/**
 * Get the monthly views for the movie.
 */
public function monthlyViews()
{
    // Group and map views to a collection of totals
    $views = $this->views
        ->groupBy(function($view) {
            return $view->created_at->format('m/y');
        })
        ->map(function ($views) {
            $view = $views->first();

            return collect([
                'id' => $view->created_at->format('m-y-').
                        $view->movie_uuid,
                'movie_id' => $view->movie_uuid,
                'total' => $views->count(),
                'label' => $view->created_at->format('m/y'),
            ]);
        });

    $months = $this->collectPastTwelveMonths();

    // Merge the views per month into all months
    return $months->merge($views);
}

/**
 * Generate zero totals for the past twelve months.
 */
protected function collectPastTwelveMonths()
{
    $months = collect();

    // Create a Period of the last 12 months
    $nextMonth = Carbon::now()
        ->addMonth()
        ->startOfMonth();
    $lastYear = $nextMonth->copy()
        ->subYear();
    $period = new Period(
        $lastYear, 
        $nextMonth
    );

    // Create zero count records for all months
    foreach ($period->getDatePeriod('1 MONTH') as $month) {
        $months->put(
            $month->format('m/y'), 
            collect([
                'id' => $month->format('m-y-').$this->uuid,
                'movie_uuid' => $this->uuid,
                'total' => 0,
                'label' => $month->format('m/y'),
            ])
        );
    }

    return $months;
}

This is a potentially a good option to go with because it allows you to generate a new collection of totals without making your database models dirty.

Option 3 - Frontend

First impulse, you could simply send all view records as a resource to your frontend. You would have a client's browser calculate the totals from all of the results using the Array.prototype map/reduce functions. However, then you have a variable amount of data being sent in the response. In the case of a personal media server, this would not be a major issue.

However, to reduce data in the response (i.e. 12 or less resource records), you could go with a variation of Option 2 where only the months with totals are sent from the backend and the totals collection is built on the frontend.

/**
 * Get the monthly views for the movie.
 */
public function monthlyViews()
{
    // Group and map views to a collection of totals
    return $this->views
        ->groupBy(function($view) {
            return $view->created_at->format('m/y');
        })
        ->map(function ($views) {
            $view = $views->first();

            return collect([
                'id' => $view->created_at->format('m-y-').
                        $view->movie_uuid,
                'movie_id' => $view->movie_uuid,
                'total' => $views->count(),
                'label' => $view->created_at->format('m/y'),
            ]);
        });
}

On the frontend, I use a package called moment and a plugin for it called moment-range to generate the zero total months. I use an iterator function on the range to merge the totals coming from the API. Here is a vuex getter that I've written to generate the necessary array for my chart.

monthlyViews: ({ movies, views }) => {
  let movie = movies.all
    .find(m => m.id === movies.currentID)
  let movieViews = movie.relationships
    .views.data.map(({ id }) => 
      views.movies.find(m => m.id === id))

  // Create a moment.js range of the past 12 months
  let currentMonth = moment().startOf('month')
  let lastYear = currentMonth.clone().subtract(11, 'M')
  let range = moment.range(lastYear, currentMonth)
  let months = []

  // Create records for past 12 months
  range.by('months', function(month) {
    let label = month.format('MM/YY')
    let view = movieViews.find(
      v => v.attributes.label === label
    )
    months.push({
      id: label,
      total: view ? view.attributes.total : 0,
    })
  })

  return months
}