Laravel中用预加载替代多查询的优化方案咨询

我正在学习提升Laravel使用技巧，现有**客户（Customer）、预约（Visit）、用户（User）**模型：用户为客户创建预约，客户可拥有多个预约。

Visit模型代码 #

class Visit extends Model
{
    use HasFactory;

    protected $fillable = [
        'user_id',
        'customer_id',
        'report',
        'appointment_date',
        'appointment_time',
    ];

    protected $dates = ['appointment_date'];

    public function user(): BelongsTo
    {
        return $this->belongsTo(User::class);
    }

    public function customer(): BelongsTo
    {
        return $this->belongsTo(Customer::class);
    }
}

Customer模型代码 #

class Customer extends Model
{
    use HasFactory;

    protected $fillable = [
        'name',
        'city',
        'address',
        'email_address',
        'phone_number',
    ];
}

现有判断脚本 #

我编写了脚本判断客户是否需创建新预约（条件：无未来预约，且最近预约超指定月数或无预约），脚本如下：

$customers = Customer::get();
foreach ($customers as $customer) {
    $Newvisit = $customer->visits()->whereInFuture()->first();
    // continue if the customer already has an appointment planned in the future
    if ($Newvisit !== null) {
        continue;
    }

    // This also retrieves visits with null appointment date, this is neccesary for appointments that are not schedulded but already planned
    $lastVisit = $customer->visits()->OrderBy('appointment_date', 'desc')->first();
    // Customer has no visits yet
    if ($lastVisit === null) {
        $this->createNewAppointment($customer);
        continue;
    }

    $lastVisitDate = new Carbon($lastVisit->appointment_date);
    // Last appointment for customer is longer than .. months ago
    if ($lastVisitDate->diffInMonths() > $this->argument('months')) {
        $this->createNewAppointment($customer);
        continue;
    }
}

疑问 #

请问能否通过预加载（eager loading）将上述逻辑优化为仅执行一次查询？因需获取未来预约和最近预约，是否无法实现该优化？

当然可以优化，而且能彻底避免循环中多次查询数据库的问题，下面提供几种高效的实现方式：

方法一：子查询整合判断条件（仅1次查询） #

直接在客户查询中通过子查询计算每个客户的「是否有未来预约」「最近预约日期」，一次查询就能拿到所有判断所需的数据：

$months = $this->argument('months');
$today = Carbon::today();

$customers = Customer::query()
    ->selectRaw('customers.*')
    // 子查询：判断当前客户是否有未来预约
    ->selectRaw('EXISTS(SELECT 1 FROM visits WHERE visits.customer_id = customers.id AND visits.appointment_date >= ?) AS has_future_visit', [$today])
    // 子查询：获取当前客户最近的预约日期（含null的未排期预约）
    ->selectRaw('(SELECT appointment_date FROM visits WHERE visits.customer_id = customers.id ORDER BY appointment_date DESC LIMIT 1) AS last_visit_date')
    ->get();

foreach ($customers as $customer) {
    if ($customer->has_future_visit) {
        continue;
    }

    // 无任何预约 或 最近预约超出指定月数
    if (is_null($customer->last_visit_date) || Carbon::parse($customer->last_visit_date)->diffInMonths($today) > $months) {
        $this->createNewAppointment($customer);
    }
}

方法二：直接筛选符合条件的客户（无需循环判断） #

如果你的目标只是找到需要创建新预约的客户，可以直接在查询中完成筛选，不用遍历所有客户：

$months = $this->argument('months');
$today = Carbon::today();
$cutoffDate = $today->subMonths($months);

$targetCustomers = Customer::query()
    // 排除有未来预约的客户
    ->whereDoesntHave('visits', function ($query) use ($today) {
        $query->where('appointment_date', '>=', $today);
    })
    // 筛选：无任何预约 或 最近预约早于 cutoffDate
    ->where(function ($query) use ($cutoffDate) {
        $query->doesntHave('visits')
            ->orWhereHas('visits', function ($subQuery) use ($cutoffDate) {
                $subQuery->where('appointment_date', '<=', $cutoffDate)
                    ->whereRaw('appointment_date = (SELECT MAX(appointment_date) FROM visits WHERE customer_id = customers.id)');
            });
    })
    ->get();

// 直接处理目标客户
foreach ($targetCustomers as $customer) {
    $this->createNewAppointment($customer);
}

关于预加载的补充说明 #

你担心的「需要获取未来和最近预约无法预加载」其实是可以实现的，但效率不如子查询：
先在Customer模型中定义lastVisit关联：

public function lastVisit(): HasOne
{
    return $this->hasOne(Visit::class)->latest('appointment_date');
}

然后用预加载获取数据：

$customers = Customer::with([
    'visits' => function ($query) use ($today) {
        $query->where('appointment_date', '>=', $today)->limit(1);
    },
    'lastVisit'
])->get();

这种方式会产生3次查询（1次查客户，2次查关联），虽然比原来的N+2次查询好，但还是不如子查询的1次高效，所以优先推荐前两种子查询方案。

内容的提问来源于stack exchange，提问作者Nemoko