Build filter scope in spatie/laravel-query-builder

It is possible to build the following with the spatie/laravel-query-builder package.

  $commandCenters = QueryBuilder::for(CommandCenter::class)
             ->allowedFilters(['id', 'name', 'imei', 'sim', 'lat', 'lon', 'address', 'ip', 'astro_clock', 'scene',
                 'manual', 'sunrise', 'sunrise_offset', 'sunset', 'sunset_offset', 'initialized_at',
                 'initialized_lums_at', 'last_update', 'manufacturer_id', 'patron', 'counter_plc', 'town_hall_id',
                 . . .
                 AllowedFilter::scope('initialized_at_between'),
                 AllowedFilter::scope('initialized_at_before'),
                 AllowedFilter::scope('initialized_at_after'),
                 AllowedFilter::scope('initialized_lums_at_between'),
                 AllowedFilter::scope('last_update_between'),
             ])

This forces me to create a scope method in the model for each of the elements that I want to filter.

public function scopeInitializedAtBetween(Builder $query, ...$dates): Builder
{
     return $query->whereBetween('initialized_at', array(Carbon::parse($dates[0]), Carbon::parse($dates[1])));
}

public function scopeInitializedAtBefore(Builder $query, $date): Builder
{
     return $query->where('initialized_at', '<=', Carbon::parse($date));
}

public function scopeInitializedAtAfter(Builder $query, $date): Builder
{
     return $query->where('initialized_at', '>=', Carbon::parse($date));
}

public function scopeInitializedLumsAtBetween(Builder $query, ...$dates): Builder
{
     return $query->whereBetween('initialized_lums_at', array(Carbon::parse($dates[0]), Carbon::parse($dates[1])));
}

public function scopeLastUpdateBetween(Builder $query, ...$dates): Builder
{
     return $query->whereBetween('last_update', array(Carbon::parse($dates[0]), Carbon::parse($dates[1])));
}

I don't know or know of a way to create a dynamic scope to pass the associated field last_update initialize_at... and its query value. without need repeat process for every column.

Is there any way to do this?

psrz

3 years ago

Best Answer

Level 10

You can do it using a custom filter:

https://spatie.be/docs/laravel-query-builder/v5/features/filtering#content-custom-filters

class InitializedFilter implements Filter
{

    public function __invoke(Builder $query, $value, string $property)
    {

         match($property) {
             'initialized_at_between' => $query->whereBetween('initialized_at', ......),
             'initialized_at_before' => $query->where('initialized_at', '<=', ......),
             'initialized_at_after' => $query->where('initialized_at', '>=', ....),
         };

    }
}

Then in your controller you set allowedFilters like this:

            ->allowedFilters(
				....
                AllowedFilter::custom('initialized_at_between', new InitializedFilter()),
                AllowedFilter::custom('initialized_at_before', new InitializedFilter()),
                AllowedFilter::custom('initialized_at_after', new InitializedFilter()),
            )

The magic happens matching the parameter $name on the method AllowedFilter::custom() and the parameter $property of the Filter class

1 like

abkrim

3 years ago

Level 13

@psrz Thanks.

With this I gain not having to do the methods one by one for a field, but I don't avoid having to create a class for each field.

abkrim

3 years ago

Level 13

@psrz Oh thanks.

It is more efficient and above all better for an app with almost 30 models and each one with at least 4 date fields. It allows me to be more dynamic for api queries, without having to rewrite code.

Call in controller

AllowedFilter::custom('initialized_at_between', new ComputedFilter),
AllowedFilter::custom('initialized_at_after, new ComputedFilter),
AllowedFilter::custom('initialized_at_before new ComputedFilter),

ComputedFilter class

<?php

namespace App\Filters;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Carbon;
use Spatie\QueryBuilder\Filters\Filter;

class ComputedFilter implements Filter
{

    /**
     * @inheritDoc
     */
    public function __invoke(Builder $query, $value, string $property)
    {
        $pos = strrpos($property, '_');
        $column = substr($property, 0, $pos);
        $suffix = substr($property, $pos + 1);

        match($property) {
            $column.'_between' => $query->whereBetween(
                $column, array(Carbon::parse($value[0]), Carbon::parse($value[1]))
            ),
            $column.'_before' => $query->where($column, '<=', Carbon::parse($value)),
            $column.'_after' => $query->where($column, '>=', Carbon::parse($value)),
        };
    }
}

1 like

Please or to participate in this conversation.