Getting an average rating of a TV show using average ratings of its seasons from ratings table (Laravel 9)

 0

I have 3 tables: shows, seasons and ratings. I would like to get average ratings of specific seasons and then make an average of this for every show and sort them by this value asc or desc. I was able to get this result by this raw query:

$shows = Show::selectRaw('shows.*, avg(x.seasons_avg_points) as ratings_avg_points')
        ->fromSub(function($query){
            $query->selectRaw('shows.id, avg(ratings.points) as seasons_avg_points')
                ->from('shows')
                ->join('seasons', 'shows.id', '=', 'seasons.show_id')
                ->leftJoin('ratings', 'ratings.item_id', '=', 'seasons.item_id')
                ->groupBy('seasons.id');
        }, 'x')
        ->join('shows', 'shows.id', '=', 'x.id')
        ->groupBy('x.id')
        ->get()->sortBy('shows_avg_points');

However I prefer to use Laravel eloquent functions so I tried also this:

$shows = Show::with(['seasons' => function($query){
        $query->withAvg('ratings', 'points');
    }])->withAvg('seasons', 'ratings_avg_points')->get();

Unfortunately, it only works without ->withAvg('seasons', 'ratings_avg_points'), as there can't be made an average of a computed column (ratings_avg_points) and it can't be found.

---

shows | id | name | |----|--------| | 1 | show 1 | | 2 | show 2 | | 3 | show 3 |

seasons | id | show_id | item_id | name | |----|---------|---------|----------| | 1 | 1 | 1 | season 1 | | 2 | 1 | 2 | season 2 | | 3 | 2 | 3 | season 1 | | 4 | 2 | 4 | season 2 | | 5 | 3 | 5 | season 1 | | 6 | 3 | 6 | season 2 |

ratings | id | item_id | points | |----|---------|--------| | 1 | 1 | 0 | | 2 | 1 | 20 | | 3 | 2 | 0 | | 4 | 2 | 30 | | 5 | 3 | 0 | | 6 | 3 | 40 | | 7 | 4 | 0 | | 8 | 4 | 50 | | 9 | 5 | 0 | | 10 | 5 | 60 | | 11 | 6 | 0 | | 12 | 6 | 70 |

result | id | name | ratings_avg_points| |----|--------|-------------------| | 3 | show 3 | 32.5 | | 2 | show 2 | 22.5 | | 1 | show 1 | 12.5 |

---

Is there a way I can get Show models with their average rating values without using that 'ugly' query above?