Check available cars query

Hi, I need a little help regarding building a query that goes and check what cars are available for set dates.

This is how I have it setup, I have a Car model and migration and a Booking model and migration. Booking belongsToMany Cars through a pivot table.

Then I have a function inside my controller were I'm collecting the booking criteria from the session, finding the cars and returning a view. But I'm struggling with the query, this is what I have so far:

public function index(Request $request)
    {
        $bookingCriteria = $request->session()->get('bookingAvailability')->get('searchTerm');

        // TODO: Find availability
        $cars = Car::whereNot(function ($query) use ($bookingCriteria) {
            $query->where([
                ['pick_up_date', '<', $bookingCriteria['fromDate']],
                ['drop_off_date', '>', $bookingCriteria['toDate']]
            ]);
        })->dd();

        // TODO: Return view
        return Inertia::render('Index', ['cars' => $cars]);
    }

It looks like ->where() doesn't recognize my variable, $bookingCriteria because it returns ? inside my query. Any idea how I can search through this pivot table and find an available car? Thanks in advance

rodrigo.pedra

3 years ago

Best Answer

Level 56

I guess you want to query the related model?

<?php

use Illuminate\Contracts\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\Route;

class Car extends Model
{
    public function bookings()
    {
        return $this->belongsToMany(Booking::class, 'booking_car');
    }
}

class Booking extends Model
{
    public function cars()
    {
        return $this->belongsToMany(Car::class, 'booking_car');
    }
}

Route::get('/', function () {
    $criteria = [
        'fomDate' => now()->addDay(),
        'toDate' => now()->addDays(3),
    ];

    $cars = Car::query()
        ->whereDoesntHave('bookings', function (Builder $builder) use ($criteria) {
            $builder->where('pick_up_date', '<', $criteria['fromDate']);
            $builder->where('drop_off_date', '>', $criteria['toDate']);
        })
        ->get();

    return $cars;
});

https://laravel.com/docs/9.x/eloquent-relationships#querying-relationship-existence

1 like

hjortur17

3 years ago

Level 14

@rodrigo.pedra - I tried this but it always find a car even if it's booked, any idea why? This is what I get when I dd the query:

^ "select * from `cars` where not exists (select * from `bookings` inner join `booking_information_pivot` on `bookings`.`id` = `booking_information_pivot`.`booking_id` where `cars`.`id` = `booking_information_pivot`.`car_id` and `pick_up_date` < ? and `drop_off_date` > ?) ◀"

rodrigo.pedra

3 years ago

Level 56

@hjortur17 well, I didn't change your query criteria, you can try this:

$cars = Car::query()
    ->whereDoesntHave('bookings', function (Builder $builder) use ($criteria) {
        $builder->where('pick_up_date', '<=', $criteria['toDate']);
        $builder->where('drop_off_date', '>=', $criteria['fromDate']);
    })
    ->get();

Note the start and end limits are "mixed" in the comparison.

In plain English this would translate to:

Select all bookings that starts before or at the provided end date AND ends after or at the provided start date.

This should do the trick.

1 like

hjortur17

3 years ago

Level 14

@rodrigo.pedra hi, not sure if you can assist my. But I noticed that I it's not showing any cars. Doesn't matter what the pick-up date. It seems like if the car has already been booked once before that it doesn't show in the list. This is how I'm getting them now:

$cars = Car::query()
            ->whereDoesntHave('booking', function (Builder $builder) use ($bookingCriteria) {
                $builder->where('pick_up_date', '<=', $bookingCriteria['toDate']);
                $builder->where('drop_off_date', '>=', $bookingCriteria['fromDate']);
            })
            ->get();

Please or to participate in this conversation.