How to sort order for whereHas relationship column in Laravel?

/
/
/
740 Views

Today I got stuck while doing sort for whereHas relationship column in Laravel. After spending a couple of hours I found a solution to accomplish sort for whereHas relationship column. So I decided to write an article about this situation.

Firstly, I want you to know the exact situation to understand exactly when and why it is needed. I was using Laravel 8.0 for this functionality. It should also be work in other Laravel versions.

The situation for whereHas relationship in Laravel to sort order:

Suppose, we have two models Videos and VideoMeta and we have following columns in both model.

  • Video Model: id, name, status, created_at, updated_at
  • VideoMeta Model: id, video_id, date, start_time

Video is the main model and VideoMeta is the relationship model for the Video model. VideoMeta model is storing other details of videos. So I created the following relationship in the Video model.

public function videoMeta()
{
	return $this->hasOne(\App\Models\VideoMeta::class, 'video_id');
}

I considered many situations to get videos by using whereHas relationship column to sort.

1. I want to get the videos which date is more than or equal to today’s date. I also want to sort videos into ascending order for the VideoMeta date column. so I write the below query for that.

$Videos = Video::where('status', 'Active')
	->whereHas('videoMeta', function($q)
	{
		$q->where('date', '>', date('Y-m-d'));
        $q->orWhere(function($q) {
            $q->where('date', '=', date('Y-m-d'))->where('start_time', '>', date('H:i:s'));
        });
	})
	->with('videoMeta')
    ->get()->sortBy('videoMeta.date');

2. Now, I want to sort using multiple columns of whereHas relationship. I want to get videos where the date is more than or equal to today’s date and I also want to get videos with ascending order of start_time so I write the below query for that.

$Videos = Video::where('status', 'Active')
	->whereHas('videoMeta', function($q)
	{
		$q->where('date', '>', date('Y-m-d'));
        $q->orWhere(function($q) {
            $q->where('date', '=', date('Y-m-d'))->where('start_time', '>', date('H:i:s'));
        });
	})
	->with('videoMeta')
    ->get()->sortBy(['videoMeta.date', 'videoMeta.start_time'])->take(6);

3. Now, I want to get a limited number of videos where the date is more than or equal to today’s date and I also want to get videos with ascending order of start_time so I write the below query for that.

$Videos = Video::where('status', 'Active')
	->whereHas('videoMeta', function($q)
	{
		$q->where('date', '>', date('Y-m-d'));
        $q->orWhere(function($q) {
            $q->where('date', '=', date('Y-m-d'))->where('start_time', '>', date('H:i:s'));
        });
	})
	->with('videoMeta')
    ->get()->sortBy(['videoMeta.date', 'videoMeta.start_time'])->take(6);

I wish this article will help you when you get that kind of situation in your Laravel project. It will help you to sort order for whereHas relationship column in Laravel.

Leave a Comment

Your email address will not be published. Required fields are marked *

This div height required for enabling the sticky sidebar