Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

KalimeroMK's avatar

Query builder not selecting data correctly

My Query builder in not selecting the correct data it selecting all data form table not according to status my code

<?php

namespace App\Modules\Search\Repositories;

use App\Modules\Fleet\Models\Fleet;
use App\Modules\Search\Exceptions\SearchVehiclesIndexException;
use App\Modules\Search\Interfaces\SearchVehiclesInterface;
use App\Modules\Vehicle\Models\Vehicle;
use App\Modules\Vehicle\Models\VehicleStatus;
use Carbon\Carbon;
use Exception;
use Illuminate\Support\Arr;
use Modules\Core\Interfaces\SearchInterface;
use Modules\Core\Repositories\Repository;

class SearchVehiclesRepository extends Repository implements SearchVehiclesInterface, SearchInterface
{
    /**
     * @var string
     */
    public $model = Vehicle::class;

    /**
     * @param array $request
     * @return mixed|void
     * @throws SearchVehiclesIndexException
     */
    public function search(array $request)
    {
        try {
            $query = $this->model::query();

            $query->where('vehicles.vehicle_status_id',VehicleStatus::STATUS_AVAILABLE);

            //Always check if vehicle has device attached
            $query->whereHasIn('device');

            if (Arr::has($request, 'fleet_id') && !is_null(Arr::get($request, 'fleet_id'))) {
                $query->where('fleet_id', '=', Arr::get($request, 'fleet_id'));
            }

            if (Arr::has($request, 'vehicle_color_id') && !is_null(Arr::get($request, 'vehicle_color_id'))) {
                $query->where('vehicle_color_id', '=', Arr::get($request, 'vehicle_color_id'));
            }

            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')))) {
                $fleet = Fleet::find(Arr::get($request, 'fleet_id'));
                $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')));

                //Todo refactor this
                $query->whereDoesntHaveIn('vehicle_rentals')
                    ->orWhereDoesntHaveIn('vehicle_rentals', function ($q) use ($request, $start_datetime, $end_datetime) {
                        $q->whereDoesntHaveIn('vehicle_rental_time', function ($subQ) use ($request, $start_datetime, $end_datetime) {
                            $subQ->where(
                                [
                                    ['booking_start_datetime', '<', $start_datetime],
                                    ['booking_end_datetime', '<', $start_datetime]
                                ])
                                ->orWhere(
                                    [
                                        ['booking_start_datetime', '>', $end_datetime],
                                        ['booking_end_datetime', '>', $end_datetime]
                                    ]);
                        });
                    });

                $query->where('fleet_id', '=', Arr::get($request, 'fleet_id'));

                if (Arr::has($request, 'vehicle_color_id') && !is_null(Arr::get($request, 'vehicle_color_id'))) {
                    $query->where('vehicle_color_id', '=', Arr::get($request, 'vehicle_color_id'));
                }
            }

            $query->orderBy(Arr::get($request, 'order_by') ?? 'id', Arr::get($request, 'sort') ?? 'desc');

            if (Arr::has($request, 'list') && (bool)Arr::get($request, 'list') === true) {
                return $query->get();
            }

            return $query->paginate(Arr::get($request, 'per_page') ?? (new $this->model)->getPerPage());

        } catch (Exception $exception) {
            throw new SearchVehiclesIndexException($exception);
        }
    }
}

my model

<?php

namespace App\Modules\Vehicle\Models;

use App\Modules\Core\Scopes\IsActiveScope;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\HasMany;

/**
 * Class VehicleStatus
 * @package App\Modules\Vehicle\Models
 */
class VehicleStatus extends Model
{
    use HasFactory;

    public const STATUS_AVAILABLE = 1;
    public const STATUS_RENTED = 2;
    public const STATUS_DEACTIVATED = 3;

    protected $table = 'vehicle_statuses';

    protected $fillable = [
        'name',
        'is_active'
    ];

    protected $dates = [
        'created_at',
        'updated_at',
    ];

    /**
     * The "booted" method of the model.
     *
     * @return void
     */
    protected static function booted()
    {
        static::addGlobalScope(new IsActiveScope);
    }

    /**
     * @return HasMany
     */
    public function vehicles(): HasMany
    {
        return $this->hasMany(Vehicle::class);
    }
}

Everything is working ok except the this selector

            $query->where('vehicles.vehicle_status_id',VehicleStatus::STATUS_AVAILABLE);

it retrieves all data form table not with status provided

0 likes
4 replies
tykus's avatar
tykus
Best Answer
Level 104

This doesn't make sense; what is whereDoesntHaveIn and orWhereDoesntHaveIn?

$query->whereDoesntHaveIn('vehicle_rentals')
    ->orWhereDoesntHaveIn('vehicle_rentals', function ($q) use ($request, $start_datetime, $end_datetime) {
        // ...
KalimeroMK's avatar

@tykus it will be refactored but don't now way

            $query->where('vehicles.vehicle_status_id',VehicleStatus::STATUS_AVAILABLE);

is overwritten not working

KalimeroMK's avatar

Code refactor fix the issue working code

$query->where(function ($query) use ($end_datetime, $request, $start_datetime) {
                    $query->whereDoesntHaveIn('vehicle_rentals')
                          ->orWhereHas('vehicle_rentals', function ($q) use ($request, $start_datetime, $end_datetime) {
                              $q->whereHas('vehicle_rental_time', function ($subQ) use ($request, $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]
                                           ]);
                              });
                          });
                });

the select was rewritten by the query who need refactoring as tykus sad

Please or to participate in this conversation.