Add a divideBy: for NumberConstarint in queryBuilder

[StaffCollab]

Package

Panel builder

Package Version

v3.2

How can we help you?

I think this is a very common scenario where you want to filter a money column, and it is cast to int * 100

But we don't have the ability to filter divided by 100. I would like some like this.

                  NumberConstraint::make('amount')
                            ->relationship(name: 'payments', titleAttribute: 'amount')
                            ->money('USD', divideBy: 100),

But obviously, there is no money method on constraints; only ontextColumns are available.

Any other way to achieve this?

[T-Mike]

Ok I had the same problem and I found a solution.
You can call the NumberConstraint and give it custom Operators.

Create 3 new Operators (I put them in app/Filament/Components/Operators). They extend filament's built-in operators and just replace the apply function with the same function but you multiply by 100 the number.

Here they are :
EqualsPriceOperator.php

<?php

namespace App\Filament\Forms\Components\Operators;

use Filament\Tables\Filters\QueryBuilder\Constraints\NumberConstraint\Operators\EqualsOperator;
use Illuminate\Database\Eloquent\Builder;

class EqualsPriceOperator extends EqualsOperator
{
    public function apply(Builder $query, string $qualifiedColumn): Builder
    {
        return $query->where($this->replaceQualifiedColumnWithQualifiedAggregateColumn($qualifiedColumn), $this->isInverse() ? '!=' : '=', floatval($this->getSettings()['number']) * 100);
    }
}

IsMinPriceOperator.php

<?php

namespace App\Filament\Forms\Components\Operators;

use Filament\Tables\Filters\QueryBuilder\Constraints\NumberConstraint\Operators\IsMinOperator;
use Illuminate\Database\Eloquent\Builder;

class IsMinPriceOperator extends IsMinOperator
{
    public function apply(Builder $query, string $qualifiedColumn): Builder
    {
        return $query->where($this->replaceQualifiedColumnWithQualifiedAggregateColumn($qualifiedColumn), $this->isInverse() ? '<' : '>=', floatval($this->getSettings()['number']) * 100);
    }
}

IsMaxPriceOperator.php

<?php

namespace App\Filament\Forms\Components\Operators;

use Filament\Tables\Filters\QueryBuilder\Constraints\NumberConstraint\Operators\IsMaxOperator;
use Illuminate\Database\Eloquent\Builder;

class IsMaxPriceOperator extends IsMaxOperator
{
    public function apply(Builder $query, string $qualifiedColumn): Builder
    {
        return $query->where($this->replaceQualifiedColumnWithQualifiedAggregateColumn($qualifiedColumn), $this->isInverse() ? '>' : '<=', floatval($this->getSettings()['number']) * 100);
    }
}

And now you can call the NumberConstraint : 🥳

<?php
...
use App\Filament\Forms\Components\Operators\EqualsPriceOperator;
use App\Filament\Forms\Components\Operators\IsMaxPriceOperator;
use App\Filament\Forms\Components\Operators\IsMinPriceOperator;
use Filament\Tables\Filters\QueryBuilder\Constraints\NumberConstraint;
use Filament\Tables\Filters\QueryBuilder\Constraints\Operators\IsFilledOperator;
...

NumberConstraint::make('amount')
    ->operators([
        IsFilledOperator::class,
        EqualsPriceOperator::class,
        IsMinPriceOperator::class,
        IsMaxPriceOperator::class,
    ])