Looking for a more elegant and reusable way

Hi, I'm currently learning Laravel and am unclear about how to best approach the following scenario.

I'm using query builder with a when condition to filter conference rooms that are either free or booked for a certain date. Currently it looks like this:

->when($this->status === 'booked', fn($query) => $query->whereHas('reservations', function (Builder $query) {$query->where('start_date','<=', $this->date)
                ->where(function($query) {
                    $query->where('end_date', '>=', $this->date)
                           ->orWhereNull('end_date');
              });}))

->when($this->status === 'free', fn($query) => $query->whereDoesntHave('reservations', function (Builder $query) {$query->where('start_date','<=', $this->date)
                ->where(function($query) {
                    $query->where('end_date', '>=', $this->date)
                           ->orWhereNull('end_date');
              });}))

As I am using the same query in other places for displaying reservations for a room, etc. so I thought I'd create a function in my model that accepts date as an input but I failed to use it in my query above.

public function currentReservations($date)
{
    return $this->hasMany(Reservation::class)
                ->where('start_date','<=', $date)
                ->where(function($query) use ($date) {
                    $query->where('end_date', '>=', $date)
                            ->orWhereNull('end_date');
                });
}

Any help is appreciated! Thank you!

Armani

3 years ago

Level 17

@katsu You can use Local Scopes: https://laravel.com/docs/9.x/eloquent#local-scopes. Also it is better to use WhereRelation method: https://laravel.com/docs/9.x/eloquent-relationships#inline-relationship-existence-queries

->when($this->status === 'booked', fn($query) => $query->whereRelation('reservations', 'start_date', '<=', $this->date));

katsu

3 years ago

Level 1

@Armani Thank you for pointing me to scopes. I will give them a try. Do you happen to know if I can use the scope in my when condition?

Regarding your suggestion to use whereRelation, the documentation says to use it 'with a single, simple where condition attached to the relationship query'. My condition also queries the end_date of the related model and therefore I assumed it wouldn't work.

Armani

3 years ago

Level 17

@katsu You have to move when statement to Local Scope:

public function scopeReserve($query, $status, $start_date, $end_date)
    {
        $query->when($status === 'booked', fn($query) => $query
               ->whereRelation('reservations', 'start_date', '<=', $start_date)
               ->whereRelation('reservations', 'end_date', '<=', $end_date));
    }

This is simple code I wrote, you should edit it for your need.

But I think it is better to use whereHas() and use whereBetween() instead of two where clauses.

Please or to participate in this conversation.