Filament•8mo ago

QueryBuilder -> NumberConstraint for Money

Hi, I am storing an amount in cents in the database as integer in field 'gross'. And I am converting cents to dollars using casting:

public function get($model, string $key, $value, array $attributes): float
    {
        // Transform the integer stored in the database into a float.
        return round(floatval($value) / 100, precision: 2);
    }

    public function set($model, string $key, $value, array $attributes): float
    {
        // Transform the float into an integer for storage.
        return round(floatval($value) * 100);
    }

public function get($model, string $key, $value, array $attributes): float
    {
        // Transform the integer stored in the database into a float.
        return round(floatval($value) / 100, precision: 2);
    }

    public function set($model, string $key, $value, array $attributes): float
    {
        // Transform the float into an integer for storage.
        return round(floatval($value) * 100);
    }

So, 100 dollars are saved in the database as 10000 (cents). Now, I want to make a NumberConstraint so the user can filter this integer field 'gross' by providing dollars (NOT cents). Input '100' should be converted to '10000' so the correct rows are filtered from the database. How do I do this??? Thanks for your help.

3 Replies

krekas•8mo ago

create a custom filter

Tables\Filters\Filter::make('price')
    ->form([
        Forms\Components\TextInput::make('from')
            ->debounce(),
        Forms\Components\TextInput::make('to')
            ->debounce(),
    ])
    ->query(function (Builder $query, array $data) {
        return $query
            ->when($data['from'], function (Builder $query, ?string $from) {
                $query->where('price', '>=', $from * 100);
            })
            ->when($data['to'], function (Builder $query, ?string $to) {
                $query->where('price', '<=', $to * 100);
            });
    }),

Tables\Filters\Filter::make('price')
    ->form([
        Forms\Components\TextInput::make('from')
            ->debounce(),
        Forms\Components\TextInput::make('to')
            ->debounce(),
    ])
    ->query(function (Builder $query, array $data) {
        return $query
            ->when($data['from'], function (Builder $query, ?string $from) {
                $query->where('price', '>=', $from * 100);
            })
            ->when($data['to'], function (Builder $query, ?string $to) {
                $query->where('price', '<=', $to * 100);
            });
    }),

ArjanOP•8mo ago

Thanks, but I am using the QueryBuilder and want to make a Contraint. How do I do this?

krekas•8mo ago

Try creating custom constraint https://filamentphp.com/docs/3.x/tables/filters/query-builder#creating-custom-constraints

Query builder - Table Builder - Filament

Gaming

Programming

QueryBuilder -> NumberConstraint for Money