Level 73
I would probably not use Eloquent for this but rather the Query builder as you do in your second example.
Another way is to use a database view and then use Eloquent on that one.
Feb 17, 2022
9
Level 41
SQL and Elocvent show diferent data
this is my SQL code and working OK
SELECT *
FROM vehicles AS v
WHERE v.id NOT IN(
SELECT vr.vehicle_id
FROM vehicle_rentals AS vr
JOIN vehicle_rental_times AS vrt
ON vr.vehicle_id = vrt.vehicle_id
WHERE vrt.booking_start_datetime <= "2022-06-07 15:00:00"
AND vrt.booking_end_datetime >= "2022-06-07 16:00:00")
but when I tray to translate to eloquent not working my eloquent code
$query = Vehicle::whereFleetId(Arr::get($data, 'fleet_id'))
->whereNotIn('vehicles.id', function ($subQ) use ($end_datetime, $start_datetime) {
return $subQ->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', '<=', $end_datetime)
->where('m.booking_end_datetime', '>=', $start_datetime);
})->get();
second way
$query = DB::table('vehicles as r')->where('fleet_id', 3)
->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 15:00:00')
->where('m.booking_end_datetime', '>=', '2022-06-07 16:00:00');
})->get();
the sql returns correct data it remove all vehicles cording to the time but the eloquent returns all date regardless of the date
Level 41
@Tray2 Pls for en example how to do it
Level 73
You create a view from your query without the where clause and then you create a model for that view and then you can use it as a normal model and to all select eloquent stuff on it like any other table.
Level 41
@Tray2 my query build is this
$query->where(function ($query) use ($end_datetime, $start_datetime) {
return $query
->whereDoesntHave('vehicle_rental_times')
->orWhereHas('vehicle_rental_times', function ($q) use ($start_datetime, $end_datetime) {
return
$q->orWhere('booking_start_datetime', '>', $end_datetime)
->orWhere('booking_end_datetime', '>', $start_datetime);
});
}
);
the date it not working it returns all rows for table regardless of date I need to filtered according to the date in if is rent don't show
Level 102
Check both queries using debugbar to spot the difference.
Level 41
@Sinnbeck i will
Level 41
$query->whereNotIn('vehicles.id', function ($subQ) use ($end_datetime, $start_datetime) {
return $subQ->select('vr.vehicle_id')->from('vehicle_rentals as vr')
->join('vehicle_rental_times as vrt', 'vr.vehicle_id', '=', 'vrt.vehicle_id')
->whereBetween('vrt.booking_start_datetime', [$start_datetime, $end_datetime])
->orWhereBetween('vrt.booking_start_datetime',
[$end_datetime, $start_datetime])
->orWhere(static function ($query) use ($start_datetime, $end_datetime) {
$query->where('vrt.booking_start_datetime', '<=', $start_datetime)
->where('vrt.booking_end_datetime', '>=', $end_datetime);
});
});
working solution ty
Please or to participate in this conversation.