custom orderBy() on constraining eager loads?

Question

my question is about the possibilty of customizing Laravel's orderBy() method of the query builder, which i am using to sort an eager loaded dataset.

This is the query scope I am using to generate the dataset. Everything works fine so far with this.

 public function scopeRestaurantsWithMenusToday($query, $city_uri){

    return $query->where('city_uri', '=', $city_uri)->with([
        'restaurants',
        'restaurants.menusToday' => function($query) {
            $query->orderBy('date', 'asc');
        }
    ]);

}

What I want to achieve is an advanced 'asc' of the used orderBy() method in the eager load constraint of 'restaurants.menusToday':

1. first all restaurants where "date == today"
2. then all restaurants where "date != today"

Can somebody help me out? Thx!

Answer 1

Something like this may work... the idea is to add an additional select which is 0 or 1 depending on if the date is today. Then you can order by that column first, then the actual date second.

public function scopeRestaurantsWithMenusToday($query, $city_uri){

    return $query->where('city_uri', '=', $city_uri)->with([
        'restaurants',
        'restaurants.menusToday' => function($query) {
            $query->addSelect(\DB::raw('if(DATE(`date`) = CURDATE(), 1, 0) as is_current_date'))->orderBy(\DB::raw('is_current_date'), 'DESC')->orderBy('date');
        }
    ]);

}

Answer 2

I tried to implement like @user3158900 suggested until I found out another problem with this that makes it quite complicated and difficult to manage then.

Here I found out a new way, simply by setting an attribue in the Restaurant model, informing about whether the restaurant has menus today (=1) or not (=0), then using this attribute in the orderBy() method. So far so good :). So I startet to use attribute setters and getters, but I don't get it working.

This - as a test of Collection integration - works just fine:

class Restaurant extends Model {
     protected $appends = ['menusTodayCheck' => 'thisTestWorks'];}

The collection returns an array "menusTodayCheck => 'thisTestWorks" in the appends part.

Does anybody know why these ways using $appends and get...() don't work?

1st fail)

class Restaurant extends Model {
protected $appends = ['menusTodayCheck'];

public function getMenusTodayCheckAttribute()
{
    return 'thisTestDoesntWork';
}}

The collection returns an array "0 => menusTodayCheck" in the appends part.

2nd fail)

class Restaurant extends Model {
    protected $appends = ['menusTodayCheck' => function() {
    return "thisTestDoesntWork";}];}

This one even throws an error: unexpected 'function' (T_FUNCTION)