How to solve the n+1 problem if hasMany needs an additional condition?

Question

Laravel

I connect a third-party table in the model and need to calculate the number of Order. If I do Eager Loading, then I get the number of all orders, but filtering by date in hasMany does not work. The date is stored in a field with the datetime format (2021-12-31 00:00:00):

class View extends Model
{
    use HasFactory;
    protected $connection = 'modx';
    protected $table = 'modx_site_partner_views';

    protected $casts = [
        'date' => 'datetime',
    ];

    // Здесь нужно добавить условие, если вообще возможно
    public function orders()
    {
        return $this->hasMany(Order::class, 'partner_id', 'partner_id');
    }

    // так получается по дополнительному запросу, но работает корректно
    public function getTotalOrdersAttribute()
    {
       return $this->hasMany(Order::class, 'partner_id', 'partner_id')->whereDate('createdon', '=', $this->date)->count();
    }
}

Answer 1

All the difficulties were due to the date. The field was datetime and it was necessary to select for the day and whereDate does not work here - you need a raw query:

<?php
View::withCount(['orders as total_orders' => function($query) {
        $query->whereRaw('date(`modx_ms2_orders`.`createdon`) = date(`modx_site_partner_views`.`date`)');
    }])
    ->where('partner_id', auth()->user()->partner_code)
    ->orderByDesc('id')
    ->paginate(10);