What's the best way to get a row through a table in Laravel?

user_8043606 0
Viewed 81

I have the following tables in my database:
Database relationship

I would like to list videos with the best rating of comments, like this:
List database content

app/Helpers/function.php

function best_comment($data, $value = 'name') {
    $properties = array();

    foreach($data->episodes as $episode) {
        if(! isset($properties['id']) || $properties['id'] > $season->bestComment[0]->id) {
            $properties['id'] = $season->bestComment[0]->id;
            $properties['name'] = $season->bestComment[0]->name;
        }
    }
    
    if(! empty($properties)) {
        return $properties[$value];
    }
}

app/Video.php

public function episodes() {
    return $this->hasMany(Episode::class);
}

app/Episode.php

public function bestComment() {
    return $this->belongsToMany(Rating::class, 'comments', 'id')->orderBy('id', 'asc')->limit(1);
}

app/Http/Controllers/VideosController.php

public function index() {
    $videos = Videos::query();

    $videos->with('episodes');

    return view('videos.index')->with('videos', $videos->paginate(4));
}

resources/views/videos/index.blade.php

@foreach($videos as $video)
    <div>
        <h5>{{ $video->title }}</h5>
        Best comment rating: {{ best_comment($video, 'name') }}
    </div>
@endforeach

With my solution there are many queries. What's the best way to print the best rated comment?

Laravel Sandbox

laravel mysql php
1 Answers

This is not the most optimal solution, but it should work.

First get the IDs of the Video models from the pagination object:

$ids = $videos->getCollection()->pluck('id');

Then use those to join the other tables, grouping by video and picking the highest rating ID. You might need to tweak this if you have other columns on your rating table, i.e. so you can rank them better than using their primary key.

$videoToRatingMap = Videos::query()
    ->whereIn('id', $ids)
    ->join('episodes', 'episodes.video_id', '=', 'videos.id')
    ->join('comments', 'comments.episode_id ', '=', 'episodes.id')
    ->join('ratings', 'ratings.id', '=', 'comments.rating_id')
    ->groupBy('videos.id')
    ->select('videos.id')
    ->selectRaw('max(ratings.id) as rating_id')
    ->get();

This will get you a map of the Videos by ID and the highest ranked comment.

This won't get you the names of the rankings however, that can be done in a separate query:

$ratingNames = Rating::query()
    ->findMany($videoToRatingMap->pluck('rating_id'))
    ->pluck('name', 'id');

Then transform the rating map to include the names:

$videoToRatingMap->transform(function($video) use ($ratingNames){
    $video['rating_name'] = $ratingNames[$video['rating_id']];
    return $video;
})->keyBy('id');

This will give you a collection, keyed by the Video IDs that also contains the rating ID and its name.

A slightly more optimal way to do this is to take the first query and wrap the whole thing into a subquery, that way you could join the ratings table on it again and get the name. Would reduce the queries by one at the cost of making the single query more complex.

user_11934660
Related