Laravel 8: order by associated table column-CodePudding

I need to order my query by a related table column:

I have a parent table flyer and the child table flyer_statistics. In the child theres an column called pageview_counter, (where is count up every page-call of the flyer).

I want to get the top 10 of the flyer, descending with the value of flyer_statistics.pageview_counter.

I tried this but it doesn't sort:

$flyer = Flyer::with(['flyerStatistics' => function ($query) {
    $query->orderBy('pageview_counter', 'desc');
}])->limit(10)->get();

Flyer model:

{
    use HasFactory;

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'affiliate_id',
        'flyer_ref_code',
        'flyer_active'
    ];

    public function flyerStatistics() {
        return $this->hasOne(FlyerStatistics::class, 'flyer_id');
    }
}

FlyerStatistics model:

{
    use HasFactory;

     /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'flyer_id',
        'pageview_counter'
    ];

    /**
     * Get the flyer that owns the phone.
     */
    public function flyer()
    {
        return $this->belongsTo(Flyer::class, 'id');
    }
}

What is my mistake?

Edit:

This would working:

Flyer::join('flyer_statistics', 'flyers.id', '=', 'flyer_statistics.flyer_id')
    ->select('affiliate_id','pageview_counter')
    ->orderBy('pageview_counter', 'desc')
    ->limit(10)
    ->get();

But is that valid in case of correct using laravel relationships?

CodePudding user response：

If its not working, you can sort it on your foreach. For example

@foreach ($flyer->flyerStatistics()->orderBy('pageview_counter', 'DESC')->get() as $flyer)

CodePudding user response：

You can use whereHas instead of with.

$flyer = Flyer::whereHas('flyerStatistics', function ($query) {
    $query->orderBy('pageview_counter', 'desc');
})->limit(10)->get();

It should work.