Laravel中如何定义带日期参数的Scope获取指定日期前最新销售数据

数据表结构 #

• Locations表
  • id
  • [...]（其他字段）
• TicketSales表
  • location_id
  • gate_date
  • number_tickets_sold

现有Model代码 #

Location模型 #

class Location extends Model
{
    use HasFactory;

    protected $table = 'locations';
    protected $primaryKey = 'id';

    protected $fillable = [
        ...
    ];

    public function ticketSales(): HasMany
    {
        return $this->hasMany(Ticketsales::class, 'location_id', 'id')
            ->where('deleted', 0);
    }
}

TicketSales模型 #

class TicketSales extends Model
{
    use HasFactory;

    protected $table = 'ticket_sales';
    protected $primaryKey = 'id';

    protected $fillable = [
        ...
    ];

    public function location(): BelongsTo
    {
        return $this->belongsTo(Location::class, 'location_id', 'id');
    }
}

现有逻辑函数 #

已写出单个地点获取指定日期前最新销售记录的函数，但需要转为可便捷链式调用的Scope：

public function latestSalesAtDate($effective_date): HasOne
{
    $carbon_date = Carbon::parse($effective_date);
    return $this->hasOne(TicketSales::class)->ofMany([
        'gate_date' => 'max',
        'id' => 'max',
    ], function (Builder $query) use ($effective_date) {
        $query->where('gate_date', '<=', $effective_date);
    });
}

示例数据 #

Locations表 #

id=1
id=2
id=3

TicketSales表 #

location_id=1, gate_date='2024-01-01', number_tickets_sold=100
location_id=1, gate_date='2024-01-02', number_tickets_sold=200
location_id=1, gate_date='2024-01-03', number_tickets_sold=300
location_id=1, gate_date='2024-01-04', number_tickets_sold=400
location_id=2, gate_date='2024-01-01', number_tickets_sold=102
location_id=2, gate_date='2024-01-02', number_tickets_sold=202
location_id=2, gate_date='2024-01-05', number_tickets_sold=302
location_id=2, gate_date='2024-01-06', number_tickets_sold=402
location_id=3, gate_date='2024-01-01', number_tickets_sold=103
location_id=3, gate_date='2024-01-02', number_tickets_sold=203
location_id=3, gate_date='2024-01-08', number_tickets_sold=303
location_id=3, gate_date='2024-01-09', number_tickets_sold=403

期望结果 #

执行$locations = Location::withLatestSalesAtDate('2024-01-05')->get();后，返回所有Location及其对应指定日期前的最新销售记录：

• Location id=1, gate_date='2024-01-04', number_tickets_sold=400
• Location id=2, gate_date='2024-01-05', number_tickets_sold=302
• Location id=3, gate_date='2024-01-02', number_tickets_sold=203

解决方案 #

方法1：定义动态Scope+关联预加载 #

在Location模型中添加动态Scope和关联方法，实现链式调用：

use Illuminate\Database\Eloquent\Builder;
use Carbon\Carbon;

// 动态Scope，支持传入日期参数
public function scopeWithLatestSalesAtDate(Builder $query, $effective_date)
{
    $query->with(['latestSalesAtDate' => function ($q) use ($effective_date) {
        $q->where('gate_date', '<=', Carbon::parse($effective_date))
          ->ofMany([
              'gate_date' => 'max',
              'id' => 'max',
          ]);
    }]);
}

// 基础关联定义
public function latestSalesAtDate(): HasOne
{
    return $this->hasOne(TicketSales::class)->where('deleted', 0);
}

调用方式完全符合需求：

$locations = Location::withLatestSalesAtDate('2024-01-05')->get();

每个Location实例的latestSalesAtDate属性就是对应日期前的最新销售记录。

方法2：子查询关联（高效无N+1） #

如果需要更高效的查询，可通过SQL子查询直接获取每个地点的最新销售数据，避免关联预加载的N+1问题：

public function scopeWithLatestSalesAtDate(Builder $query, $effective_date)
{
    // 子查询：获取每个地点在指定日期前的最大gate_date
    $maxDateSubquery = TicketSales::selectRaw('location_id, MAX(gate_date) as max_gate_date')
        ->where('gate_date', '<=', Carbon::parse($effective_date))
        ->where('deleted', 0)
        ->groupBy('location_id');

    $query->leftJoinSub($maxDateSubquery, 'latest_sales_dates', function ($join) {
        $join->on('locations.id', '=', 'latest_sales_dates.location_id');
    })
    ->leftJoin('ticket_sales', function ($join) {
        $join->on('locations.id', '=', 'ticket_sales.location_id')
             ->on('ticket_sales.gate_date', '=', 'latest_sales_dates.max_gate_date')
             ->where('ticket_sales.deleted', 0);
    })
    ->select('locations.*', 'ticket_sales.gate_date', 'ticket_sales.number_tickets_sold');
}

这种方式返回的Location结果中会直接包含gate_date和number_tickets_sold字段，无需通过关联属性访问，查询效率更高。

注意事项 #

• 确保TicketSales类名拼写正确（原代码中hasMany(Ticketsales::class)注意大小写，Laravel默认驼峰命名应为TicketSales::class）
• 用Carbon::parse处理日期参数，避免无效日期格式导致的错误
• 保留deleted=0的条件，与原ticketSales关联的过滤逻辑保持一致

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