Need to list all vehicles form vehicles table who are available for given start_date and end_date but the date are in vehicle_rental_times table not in vehicles table so if vehicles is rent and start and end date is the same as give date i the src parameter to be remove not shown in the list or the time is not the same to be show in the list
my code
$query = Vehicle::query();
$query->whereDoesntHave('vehicle_rental_times')
->orWhereHas('vehicle_rental_times', function ($q) use ($start_datetime, $end_datetime) {
return $q->orWhere('booking_end_datetime', '<', $start_datetime)
->orWhere('booking_start_datetime', '>', $end_datetime);
}
);
relation in model Vehicle
/**
* @return HasMany
*/
public function vehicle_rental_times(): HasMany
{
return $this->hasMany(VehicleRentalTime::class);
}
@fylzero to be clear for an example: Vehicle with id 10 is rent it will show in vehicle_rental_times with star and end time what I need is away to show Vehicle with id 10 every time except when is ask for period when is already rent
this is my SQL it working nice need to translate to query builder
SELECT *
FROM vehicles AS r
WHERE r.id NOT IN(
SELECT m.vehicle_id
FROM vehicle_rentals AS b
JOIN vehicle_rental_times AS m
ON b.vehicle_id = m.vehicle_id
WHERE m.booking_start_datetime < '2022-06-07 17:00:00'
AND m.booking_end_datetime > '2022-06-07 15:00:00'
DB::table('vehicles as r')
->whereNotIn('r.id', function ($query) {
return $query->select('m.vehicle_id')->from('vehicle_rentals as b')
->join('vehicle_rental_times as m', 'b.vehicle_id', '=', 'm.vehicle_id')
->where('m.booking_start_datetime', '<', '2022-06-07 17:00:00')
->where('m.booking_end_datetime', '>', '2022-06-07 15:00:00');
})->get();
Outputs:
"select * from `vehicles` as `r` where `r`.`id` not in (select `m`.`vehicle_id` from `vehicle_rentals` as `b` inner join `vehicle_rental_times` as `m` on `b`.`vehicle_id` = `m`.`vehicle_id` where `m`.`booking_start_datetime` < ? and `m`.`booking_end_datetime` > ?)"
