Filament•4w ago

Table Query Builder with Constraints

Hello everyone, I'm working on a resource with aggregated columns ( SUM() ) where date between I've created a from date -> to date filter which works fine but now I wanted to use the QueryBuilder constraints llike NumberConstraint and I have created a new Operator which should apply a having('column_name', '>', value) but in the final query the having clause is not applied if I were to dump the query for the operator it always shows a simple query without the aggregated columns even if I overriden the getEloquentQuery method and also using query() method on the table with the exact same query. Any advice on how to handle the having and have it applied on the final query ? Thanks you for your help.

16 Replies

biebthesecond•4w ago

Could you maybe share some code for context?

M3TAGH0STOP•4w ago

I don't have currently access to the code, tomorrow morning I could share a snippet, what part are you interested in seeing ?

biebthesecond•4w ago

So to make it clear for myself, you're creating a table filter that lets the user select a column, operator and value they want to filter with? That filter result needs to be processed in a sql "Having" clause because the use of aggregated columns. Currently when trying to add that to the query builder its not adding the having clause?

M3TAGH0STOP•4w ago

and. the custom HavingMinOperator

<?php

namespace App\Filament\App\QueryBuilder\Constraints\Operators;

class HavingMinOperator extends Operator
{
    public function getName(): string
    {
        return 'havingMin';
    }

    public function getLabel(): string
    {
        // For demonstration, a simple label:
        return 'Greater Than';
    }

    public function getSummary(): string
    {
        return 'Having Min: ' . Number::format($this->getSettings()['number']);
    }

    public function getFormSchema(): array
    {
        return [
            TextInput::make('number')
                ->label('Min Value')
                ->numeric()
                ->required(),
        ];
    }

    public function apply(Builder $query, string $qualifiedColumn): Builder
    {
        $value = $this->getSettings()['number'] ?? null;

        if (is_numeric($value)) {
            // Because this is an aggregator, we do HAVING:
            $query->having($qualifiedColumn, '>', $value);
        }

        dump($query->toRawSql()); // For debugging: remove later
        return $query;
    }
}

<?php

namespace App\Filament\App\QueryBuilder\Constraints\Operators;

class HavingMinOperator extends Operator
{
    public function getName(): string
    {
        return 'havingMin';
    }

    public function getLabel(): string
    {
        // For demonstration, a simple label:
        return 'Greater Than';
    }

    public function getSummary(): string
    {
        return 'Having Min: ' . Number::format($this->getSettings()['number']);
    }

    public function getFormSchema(): array
    {
        return [
            TextInput::make('number')
                ->label('Min Value')
                ->numeric()
                ->required(),
        ];
    }

    public function apply(Builder $query, string $qualifiedColumn): Builder
    {
        $value = $this->getSettings()['number'] ?? null;

        if (is_numeric($value)) {
            // Because this is an aggregator, we do HAVING:
            $query->having($qualifiedColumn, '>', $value);
        }

        dump($query->toRawSql()); // For debugging: remove later
        return $query;
    }
}

message.txt

biebthesecond•4w ago

Mmmh, that is quite weird

M3TAGH0STOP•4w ago

I think i must mention that last week I started working with filament so i'm kinda new to the game.. not really sure if I could call it a filter, seems more like a constraint for me that create dynamic rules to be applied

biebthesecond•4w ago

It is triggering the

$query->having($qualifiedColumn, '>', $value);

You seem to be doing quite well for working with it for just a week haha

M3TAGH0STOP•4w ago

well I didn't add the inverse logic in it, since it's a rough version and I couldn't make it work I just set by default >

biebthesecond•4w ago

But why use a query builder? Is there a reason a "normal" filter wouldn't work? I haven't actually used query builders myself yet, so I don't know the full extend of the functionality that's available

M3TAGH0STOP•4w ago

Using Tables\Filters\Filter::make to create a minimum and max fields in the form and apply there using baseQuery() applies correctly having on the final query, but I have like 8 columns in the table that I need to have a minimum amount and maximum amount, and the feature provided by the constraints on query builder where you can add rules to it, it's exactly what I would need.

biebthesecond•4w ago

Okay that does make sense So you're not sure if the "$query->having($qualifiedColumn, '>', $value);" is being triggered? Maybe try to debug if adding anything to the query works there and see what it ends up with?

M3TAGH0STOP•4w ago

It's getting triggered correctly when I want to apply the rule but the $query if I dump it there would look like this

SELECT * FROM table_name WHERE date >= '2025-01-15' AND date <= '2025-01-16' having sum_of_total_amount > 15

SELECT * FROM table_name WHERE date >= '2025-01-15' AND date <= '2025-01-16' having sum_of_total_amount > 15

Where my final base table query would look like this if I look in the debug bar

SELECT
    SUM(amount) as sum_of_total_amount
FROM database.table_name
WHERE
    date >= '2025-01-15' AND date <= '2025-01-16'

SELECT
    SUM(amount) as sum_of_total_amount
FROM database.table_name
WHERE
    date >= '2025-01-15' AND date <= '2025-01-16'

I think I skipped an outer select from the second cannot remmeber from the memory, but basically what is passed to the apply method from the operator is not the same query, I'm not even sure from where that query comes since it doesn't contain the aggregated columns, for a normal "filter" if I remember correctly I did like ->baseQuery(... return $query->having()) and it worked without any issue. 😄

biebthesecond•4w ago

Oh like that, yeah that could be the case 🤔 Sounds like some deep Filament black magic 😵‍💫

M3TAGH0STOP•4w ago

The default NumberConstraint::class constraint is using where basically I would just create an identical class and instead of where would be having but didn't work as smooth as I thought 😄 What I believe the issue would is that I'm managing to do my operator to apply the having on the base query 😄 which I'm stuck and got no clue on how to do it from that position :))

biebthesecond•4w ago

Mmmmh, although I'd love to. I'm not sure if I have a solution :( For example, if you were to add a normal "IsGreaterThan" operator which would add a "WHERE". Is that appended to the final query? (the one you see in your debugbar)

M3TAGH0STOP•4w ago

if I remember correctly I believe so, but I will do a quick check tomorrow morning and let you know 🙂

Gaming

Programming

Table Query Builder with Constraints

Did you find this page helpful?