Qery builder help

hi need help

my qery bulder code

if ((Arr::has($request, 'start_datetime') && ! is_null(Arr::get($request, 'start_datetime')))
                    && (Arr::has($request, 'end_datetime') && ! is_null(Arr::get($request, 'end_datetime')))) {
                    $start_datetime = Carbon::parse(Carbon::createFromFormat('Y-m-d H:i', Arr::get($request, 'start_datetime')));
                    $end_datetime   = Carbon::parse(Carbon::createFromFormat('Y-m-d H:i', Arr::get($request, 'end_datetime')));
                    $query->where(function ($query) use ($end_datetime, $start_datetime) {
                        $query->whereDoesntHaveIn('vehicle_rentals')
                              ->orWhereHas('vehicle_rentals', function ($q) use ($start_datetime, $end_datetime) {
                                  $q->whereHas('vehicle_rental_time', function ($subQ) use ($start_datetime, $end_datetime) {
                                      $subQ->where(
                                          [
                                              ['booking_start_datetime', '<', $start_datetime],
                                              ['booking_end_datetime', '<', $start_datetime],
                                          ])
                                           ->Where(
                                               [
                                                   ['booking_start_datetime', '>', $end_datetime],
                                                   ['booking_end_datetime', '>', $end_datetime],
                                               ]);
                                  });
                              });
                    });
                }

need to sho all avelable vehicle after end date or before start date but getting empty array

rodrigo.pedra

4 years ago

Best Answer

Level 56

Try this:

if ($request->filled('start_datetime', 'end_datetime')) {
    $start_datetime = $request->date('start_datetime', 'Y-m-d H:i');
    $end_datetime = $request->date('end_datetime', 'Y-m-d H:i');

    $query->where(fn ($query) => $query
        ->whereDoesntHave('vehicle_rentals')
        ->orWhereHas('vehicle_rentals', fn ($q) => $q
            ->whereHas('vehicle_rental_time', fn ($subQ) => $subQ
                ->orWhere('booking_end_datetime', '<', $start_datetime)
                ->orWhere('booking_start_datetime', '>', $end_datetime)
            )
        )
    );
}

The main change was to use ->orWhere(...) on the deepest subquery.

All other changes were cosmetic or simplifications.

The OR is needed because before it reads:

Give me all rentals that started and ended before start_datetime AND started and ended after end_datetime

Assuming start_datetime happened before end_datetime this condition would never be true.

I simplified this part to this:

Give me all rentals that ended before start_datetime OR started after end_datetime

If your database has booking_start_datetime always before booking_end_datetime, you don't need to check both start and end dates on each condition.

Hope this helps.

Please or to participate in this conversation.