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