Laravel scope with parameter

I have a laravel application with a simple (I think structure): Tables: Locations id [...]

TicketSales location_id gate_date number_tickets_sold

I have the following Models:

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);
        }
  }

and

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');
    }
}

The sales being date-based, I'm trying to write a scope that would accept one date parameter and return the latest sales for locations prior to that date.

I've created a function to show the logic I want to use, I'm just not sure how to turn it into a scope so it can be used easily:

    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);
        });
    }

For example, if I have the following data:

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

I would want

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

to return the list of all locations, with the last recorded sales for each 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

How do I define that scope?

So you want a Local Query Scope that needs to load a relationship with some condition. You define it as you would any other scope.

public function scopeWithLatestSalesData(Builder $query, $date)
{
    $query->with([
        'ticketSales' => fn ($relation) => $relation->where('gate_date', '<=', $date),
    ]);
}

public function ticketSales(): HasMany
{
    return $this->hasMany(TicketSales::class);
}

However, bear in mind this won't work if you want to load it and load the whole ticketSales relationship at the same time.