Filament•17mo ago

Using ->having on a query when using a TernaryFilter

I'm trying to filter my Filament table using a ->having query with Filament, but the having query doesn't appear to do anything. This is my filter:

TernaryFilter::make('minimum_invoice_amount')
                    ->placeholder('All invoices')
                    ->trueLabel('Invoiceable amount above € 5')
                    ->falseLabel('Invoiceable amount below € 5')
                    ->queries(
                        true: fn (Builder $query) => $query->having('total_price_excl_vat', '>=', 5),
                        false: fn (Builder $query) => $query->having('total_price_excl_vat', '<=', 5),
                        blank: fn (Builder $query) => $query,
                    )

TernaryFilter::make('minimum_invoice_amount')
                    ->placeholder('All invoices')
                    ->trueLabel('Invoiceable amount above € 5')
                    ->falseLabel('Invoiceable amount below € 5')
                    ->queries(
                        true: fn (Builder $query) => $query->having('total_price_excl_vat', '>=', 5),
                        false: fn (Builder $query) => $query->having('total_price_excl_vat', '<=', 5),
                        blank: fn (Builder $query) => $query,
                    )

And my table query:

        return parent::getTableQuery()
            ->selectRaw('invoices.*, hostbill_clients.id as hostbill_client_id, SUM(unit_price*quantity) as total_price_excl_vat')
            ->join('invoice_lines', 'invoice_lines.invoice_id', '=', 'invoices.id')
            ->join('external_customers', 'external_customers.id', '=', 'invoices.external_customer_id')
            ->leftJoin('hostbill_clients', 'hostbill_clients.id', '=', 'external_customers.hostbill_client_id')
            ->groupBy('invoices.id');

        return parent::getTableQuery()
            ->selectRaw('invoices.*, hostbill_clients.id as hostbill_client_id, SUM(unit_price*quantity) as total_price_excl_vat')
            ->join('invoice_lines', 'invoice_lines.invoice_id', '=', 'invoices.id')
            ->join('external_customers', 'external_customers.id', '=', 'invoices.external_customer_id')
            ->leftJoin('hostbill_clients', 'hostbill_clients.id', '=', 'external_customers.hostbill_client_id')
            ->groupBy('invoices.id');

The filters themselves work fine, I tested it with the following:

->queries(
    true: fn (Builder $query) => $query->whereRaw('1=2')->having('total_price_excl_vat', '>=', 5),
    false: fn (Builder $query) => $query->whereRaw('1=1')->having('total_price_excl_vat', '<=', 5),
    blank: fn (Builder $query) => $query->whereRaw('1=1'),
)

->queries(
    true: fn (Builder $query) => $query->whereRaw('1=2')->having('total_price_excl_vat', '>=', 5),
    false: fn (Builder $query) => $query->whereRaw('1=1')->having('total_price_excl_vat', '<=', 5),
    blank: fn (Builder $query) => $query->whereRaw('1=1'),
)

In that case, the 'true' option gave no results, but the bottom two did. Removing the whereRaw line from all filters makes them all return the full table. It appears that the having statement gets ignored? I get no errors, but changing the filters doesn't do anything. Manually adding the filter to my table query does work. Does anyone know how I could fix this?

5 Replies

wotroeOP•17mo ago

https://github.com/filamentphp/filament/discussions/6753 Looks like I'm running into a similar issue to this

GitHub

Example trashed/deleted_at TernaryFilter only works if I include wi...

I'm using a table with filters, specifically the example in the documentation. protected function getTableFilters(): array { return [ TernaryFilter::make('trashed') ->nullable() ->...

wotroeOP•17mo ago

That was it! Quickly bodged this together: https://gist.github.com/MrWouterNL/5cf14e4baf7b333039c15efddb4e9914

Gist

MinimumInvoiceValueFilter.php

GitHub Gist: instantly share code, notes, and snippets.

wotroeOP•17mo ago

How would this work on Filament 3? @Dan Harrin on github mentioned something about having a baseQuery() method so I wouldn't need to make a custom filter didn't mean to mention! sorry 😅

Dan Harrin•17mo ago

->baseQuery(fn ($query, $data) => $query->having()) i guess

wotroeOP•17mo ago

thanks!

Gaming

Programming

Using ->having on a query when using a TernaryFilter