Level 50
It seems that your logic is difficult. I suggest to use filtering may be not on sql but on php and add caching that logic
May be also do refactoring code to simplify code, because now it hard to read and find the problems
Feb 12, 2025
3
Level 1
Help with filters and dynamic sorting in Livewire/Laravel
I am using Livewire, Laravel, and Alpine.js/Choices.js for some UI components.
There is a table that displays vehicle records. I seeded the local database with 3,000 vehicles, but the page freezes when I try to apply a filter. I’m only showing parts of the code here. The filters are dependent, meaning when the user selects an option in one filter, all other filters must update to show only options associated with vehicles that match the selected criteria.
There are 14 filter options in total. There are 8 selects options with model like fuel, origin. There are 4 input that the user can write prices, kms or registration date. And a search bar. getFuelOptions is a exemple, there is a function like this for every select.
Here is the code for my Livewire component:
protected function getFuelOptions(array $filters = []): array
{
return Fuel::whereHas('vehicles', function ($query) use ($filters) {
if (! empty($filters['originsIds'])) {
$query->whereIn('origin_id', $filters['originsIds']);
}
if (! empty($filters['bodyTypesIds'])) {
$query->whereIn('body_type_id', $filters['bodyTypesIds']);
}
if (! empty($filters['colorsIds'])) {
$query->whereIn('color_id', $filters['colorsIds']);
}
if (! empty($filters['brandsIds'])) {
$query->whereIn('brand_id', $filters['brandsIds']);
}
if (! empty($filters['modelsIds'])) {
$query->whereIn('model_id', $filters['modelsIds']);
}
if (! empty($filters['versionsIds'])) {
$query->whereIn('version_id', $filters['versionsIds']);
}
if (! empty($filters['min_date'])) {
$query->whereDate('registration_plate_date', '>=', $filters['min_date']);
}
if (! empty($filters['max_date'])) {
$query->whereDate('registration_plate_date', '<=', $filters['max_date']);
}
if (! empty($filters['min_kms'])) {
$query->where('kms', '>=', $filters['min_kms']);
}
if (! empty($filters['max_kms'])) {
$query->where('kms', '<=', $filters['max_kms']);
}
if (! empty($filters['equipment'])) {
$flatEquipment = collect($filters['equipment'])->flatten()->map(fn ($item) => (int) $item)->toArray();
$equipmentValues = Vehicle::whereIn('id', $flatEquipment)->pluck('equipment')->toArray();
$query->whereIn('equipment', $equipmentValues);
}
if (! empty($this->priceFilters)) {
foreach ($this->priceFilters as $priceTypeId => $priceFilter) {
$query->when(! empty($priceFilter['min']), function ($query) use ($priceFilter, $priceTypeId) {
$query->whereHas('prices', function ($query) use ($priceFilter, $priceTypeId) {
$query->where('value', '>=', $priceFilter['min'])
->where('vehicle_price_type_id', $priceTypeId);
});
});
$query->when(! empty($priceFilter['max']), function ($query) use ($priceFilter, $priceTypeId) {
$query->whereHas('prices', function ($query) use ($priceFilter, $priceTypeId) {
$query->where('value', '<=', $priceFilter['max'])
->where('vehicle_price_type_id', $priceTypeId);
});
});
}
}
})
->get(['id', 'name'])
->map(fn ($fuel) => ['value' => $fuel->id, 'label' => $fuel->name])
->toArray();
}
public function render()
{
// search by vehicle id to find equipments value
if (! empty($this->equipmentsFilter)) {
$flatEquipment = collect($this->equipmentsFilter)->flatten()->map(function ($item) {
return (int) $item;
})->toArray();
$equipmentsFiltered = Vehicle::whereIn('id', $flatEquipment)->pluck('equipment')->toArray();
}
$vehicles = Vehicle::with([
'origin',
'color',
'body_type',
'fuel',
'prices',
'prices.vehicle_price_type',
'brand',
'version',
'model',
'vehicleable',
'user',
'detailed_color',
'extras',
'images',
])
->where(function ($query) {
$query->where('registration_plate', 'like', '%'.$this->tableSearch.'%')
->orWhere('kms', 'like', '%'.$this->tableSearch.'%')
->orWhere('vin', 'like', '%'.$this->tableSearch.'%')
->orWhere('komission', 'like', '%'.$this->tableSearch.'%')
->orWhereHas('origin', fn ($query) => $query->where('name', 'like', '%'.$this->tableSearch.'%'))
->orWhereHas('color', fn ($query) => $query->where('name', 'like', '%'.$this->tableSearch.'%'))
->orWhereHas('body_type', fn ($query) => $query->where('name', 'like', '%'.$this->tableSearch.'%'))
->orWhereHas('fuel', fn ($query) => $query->where('name', 'like', '%'.$this->tableSearch.'%'))
->orWhereHas('brand', fn ($query) => $query->where('name', 'like', '%'.$this->tableSearch.'%'))
->orWhereHas('version', fn ($query) => $query->where('name', 'like', '%'.$this->tableSearch.'%'))
->orWhereHas('model', fn ($query) => $query->where('name', 'like', '%'.$this->tableSearch.'%'))
->orWhereHas('prices', fn ($query) => $query->where('value', 'like', '%'.$this->tableSearch.'%'));
})
->when(! empty($this->priceFilters), function ($query) {
foreach ($this->priceFilters as $priceTypeId => $priceFilter) {
if (! empty($priceFilter['min'])) {
$query->whereHas('prices', function ($query) use ($priceFilter, $priceTypeId) {
$query->where('value', '>=', $priceFilter['min'])
->where('vehicle_price_type_id', $priceTypeId);
});
}
if (! empty($priceFilter['max'])) {
$query->whereHas('prices', function ($query) use ($priceFilter, $priceTypeId) {
$query->where('value', '<=', $priceFilter['max'])
->where('vehicle_price_type_id', $priceTypeId);
});
}
}
})
->when(! empty($this->min_date), function ($query) {
$query->whereDate('registration_plate_date', '>=', $this->min_date);
})
->when(! empty($this->max_date), function ($query) {
$query->whereDate('registration_plate_date', '<=', $this->max_date);
})
->when(! empty($this->min_kms), function ($query) {
$query->where('kms', '>=', $this->min_kms);
})
->when(! empty($this->max_kms), function ($query) {
$query->where('kms', '<=', $this->max_kms);
})
->when($this->equipmentsFilter, fn ($query) => $query->whereIn('equipment', $equipmentsFiltered))
->when($this->originFilter, fn ($query) => $query->whereIn('origin_id', $this->originFilter))
->when($this->bodyTypeFilter, fn ($query) => $query->whereIn('body_type_id', $this->bodyTypeFilter))
->when($this->colorsFilter, fn ($query) => $query->whereIn('color_id', $this->colorsFilter))
->when($this->brandsFilter, fn ($query) => $query->whereIn('brand_id', $this->brandsFilter))
->when($this->versionsFilter, fn ($query) => $query->whereIn('version_id', $this->versionsFilter))
->when($this->modelsFilter, fn ($query) => $query->whereIn('model_id', $this->modelsFilter))
->when($this->fuelsFilter, fn ($query) => $query->whereIn('fuel_id', $this->fuelsFilter))
->when($this->sortColumn, function ($query) {
if (strpos($this->sortColumn, 'price_type_') === 0) {
$priceTypeId = substr($this->sortColumn, 11);
$query->orderByRaw('(
SELECT value
FROM vehicle_prices
WHERE vehicle_prices.vehicle_id = vehicles.id
AND vehicle_prices.vehicle_price_type_id = '.$priceTypeId.') '.$this->sortDirection);
} elseif ($this->sortColumn === 'owner_type') {
$query->orderByRaw('CASE
WHEN vehicleable_type = ? THEN
(SELECT CONCAT(first_name, " ", last_name)
FROM customers
WHERE customers.id = vehicles.vehicleable_id)
WHEN vehicleable_type = ? THEN
(SELECT name
FROM companies
WHERE companies.id = vehicles.vehicleable_id)
ELSE "N/A"
END '.$this->sortDirection, [
\App\Models\Tenant\Customer::class,
\App\Models\Tenant\Company::class,
]);
} else {
$query->orderBy($this->sortColumn, $this->sortDirection);
}
})
->paginate(10);
return view('livewire.vehicle-secretary-table', [
'vehicles' => $vehicles,
'domain' => $this->domain,
'tenantId' => $this->tenantId,
'origins' => $this->origins,
'bodyTypes' => $this->bodyTypes,
'colors' => $this->colors,
'brands' => $this->brands,
'versions' => $this->versions,
'model' => $this->models,
'equipments' => $this->equipments,
'fuels' => $this->fuels,
]);
}
This is the back end logic on the component. For the blade.php file, i used choices to show the filters in selects
<div class="grid gap-y-2" style="color: black;" wire:ignore>
<div class="min-w-0 flex-1" style="border: 1px solid gray; border-radius: 10px;" wire:ignore
x-data="{
value: @entangle('fuelsFilter'),
options: @js($fuels),
debounce: null,
}" x-init="
$nextTick(() => {
const choices = new Choices($refs.select, {
removeItems: true,
removeItemButton: true,
duplicateItemsAllowed: false,
placeholderValue: 'Combustível',
noChoicesText: 'Nenhuma opção sobrando'
})
const refreshChoices = () => {
const selection = Array.isArray(value) ? value : [value];
choices.clearStore();
choices.setChoices(options.map(({ value, label }) => ({
value,
label,
selected: selection.includes(value),
})));
}
$refs.select.addEventListener('change', () => {
this.value = choices.getValue(true);
$wire.call('updateFuelFilter', choices.getValue(true));
})
$wire.on('select-options-updated', (updatedOptions) => {
options = updatedOptions;
})
$wire.on('select-options-updated-fuel', (updatedOptions) => {
options = Array.isArray(updatedOptions[0]) ? updatedOptions[0] : updatedOptions;
refreshChoices();
});
$watch('value', () => refreshChoices());
$watch('options', () => refreshChoices());
refreshChoices();
})
">
<select x-ref="select" multiple></select>
</div>
</div>
How can I optimize this process to avoid freezing and improve performance?
Level 102
If the page freezes and you are doing the request using ajax, I dont see any reason it would be php that is the problem (unless you make an ajax request and then try refreshing the page, and are using php artisan serve as it is single threaded)
Level 1
Looking at some of your queries, they could do with some improvement, you can expand where functions maybe with some of your foreach loops? You could start by commenting out some queries which you think can cause the issues to pinpoint it. Also include try catch's in places and if you moved your code out to a $query at the start, you can add debug line by line to see where the code ran too.
$query->where(function() {
} );
Please or to participate in this conversation.