Laravel 5.8 - How sort (orderBy) multiple relationship

Question

I am trying to sort the serials by video views.

Relations: The Serial has a hasMany relationship to series. The Series has a hasMany relationship to episodes. The Episodes has a hasOne relationship to video. The Video has a hasMany relationship to viewcounts.

<?php
//sort method:
public function mostPopular()
    {

        $serials = Serial::with(['series.episodes.video' => function ($query) {
            $query->withCount(['videoViews' => function($query) {
            }])->orderBy('video_views_count', 'desc');
        }])->get();

        return $serials;
}

//Serial model:
public function series()
{
   return $this->hasMany(Series::class);
}


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

public function serial()
{
   return $this->belongsTo(Serial::class);
}

//Episode model:
public function video()
{
   return $this->hasOne(Video::class);
}

public function series()
{
   return $this->belongsTo(Series::class);
}

//Video model:
public function videoViews()
{
   return $this->hasMany(VideoView::class);
}

public function episode()
{
   return $this->belongsTo(Episode::class);
}


?>

I expect the sorted serials by video views (series.episodes.video.videoViews), but the actual output is not sorted.

Laravel 5.8 PHP 7

I'm curious about the empty function body on the withCount constraint. ['videoViews' => function($query) { }]. If there's no extra logic to counting, you should leave the countWith with just the string name of the relationship: $query->withCount(['videoViews']). That might not be the underlying issue. — j.steelman
– j.steelman, Commented Jun 4, 2019 at 17:36
This really is an interesting question. Counting relations that are several times removed through several hasManyThrough relations is not something I've seen discussed before. If performance becomes an issue, you might consider caching the answer. Redis loves ordered lists or you can just store the series_ids in an array and update it every hour. Or you could store an indexed "series_id" in your video_views table which would allow you to leap-frog over the episode model and hit the video_views table directly with a simple withCount. — j.steelman
– j.steelman, Commented Jun 4, 2019 at 19:34

anoraq · Accepted Answer · 2019-10-22 11:42:47Z

1

This is a silly one actually but I've learnt that multiple ->sortBy on collections actually are possible with no workarounds. It's just that you need to reverse the order of them. So, to sort a catalogue of artists with their album titles this would be the solution...

Instead of :

$collection->sortBy('artist')->sortBy('title');

Do this :

$collection->sortBy('title')->sortBy('artist');

answered Oct 22, 2019 at 11:42

anoraq

5157 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

j.steelman · Accepted Answer · 2019-06-04 19:25:41Z

0

Because "With" queries run as seperate queries (not subqueries as previously suggested), exposing extrapolated fuax-columns from one query to the other gets rather tricky. I'm sure there's non-documented solution in the API docs but I've never come across it. You could try putting your with and withCount in the orderBy:

Serial::orderBy(function($query) { some combo of with and withCount })

But that too will get tricky. Since either approach will hit the database multiple times, it would be just as performant to do the separation yourself and keep your sanity at the same time. This first query uses a left join, raw group by and raw select because I don't want laravel running the with query as a separate query (the problem in the first place).

$seriesWithViewCounts = VideoView::leftJoin('episodes', 'episodes.id', '=', 'video_views.episode_id')
                ->groupBy(DB::raw('episodes.series_id'))
                ->selectRaw("episodes.series_id, count(video_views.id) as views")
                ->get();
$series = Series::findMany($seriesWithViewCounts->pluck('series_id'));
foreach($series as $s) {
    $s->view_count = $seriesWithViewCounts->first(function($value, $key) use ($s) {
       return $value->series_id = $s->id
    })->video_views_count;
});
$sortedSeries = $series->sortBy('video_views_count');

This will ignore any series that has no views for all episodes, so you may want to grab those and append it to the end. Not my definition of "popular".

I'd love to see a more eloquent way of handling this, but this would do the job.

edited Jun 4, 2019 at 19:25

answered Jun 4, 2019 at 17:39

j.steelman

4393 silver badges7 bronze badges

2 Comments

andrej_14 Over a year ago

Its not working... "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'video_views_count' in 'order clause' (SQL: select * from serials order by video_views_count desc)"

j.steelman Over a year ago

Sorry, forgot laravel subqueries run separately. Edited answer with a new approach. :-P

Collectives™ on Stack Overflow

Laravel 5.8 - How sort (orderBy) multiple relationship

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related