0
318
ViewsWhat's the best way to get a row through a table in Laravel?
I have the following tables in my database:
I would like to list videos with the best rating of comments, like this:
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?
1 answers
Answer question0
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.