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.