Filament•11mo ago

Custom Filter and Query Scope

Hi everyone, I'm facing some difficulties understanding how Filament behaves behind the scenes regarding custom filters and queries. To elaborate further: I have a resource displaying a table linked to a Laravel model. However, the table is extremely large (with several million records), and I've applied a basic scope to filter the query. MyScope: I take the latest record, read the value of a column (lunivid_serie) , and filter the entire query for the found value:

public function apply(Builder $builder, Model $model): void
    {

        $latestSeries = DB::table('kiid_mail_report_d')
                    ->select('*')
                    ->orderByDesc('tuts')
                    ->first();


        $builder->where('lunivid_serie', '=', $latestSeries->lunivid_serie);
    }

public function apply(Builder $builder, Model $model): void
    {

        $latestSeries = DB::table('kiid_mail_report_d')
                    ->select('*')
                    ->orderByDesc('tuts')
                    ->first();


        $builder->where('lunivid_serie', '=', $latestSeries->lunivid_serie);
    }

The issue arises when, through a custom filter, I try to provide the ability to modify this filter. I'm attempting to access the $query variable using the withoutGlobalScopes() method, but the query fails without an apparent reason (the records exist and are present; when I do dd($query), everything seems to be correctly set up). This filter is NOT working...

code in the comment...

code in the comment...

Can anyone explain to me how to have a general filter ONLY on the table and a dynamically custom filter based on the filter, so that the querydoesn't start with the same filter set to initially display the data in the table? It seems like an extremely straightforward operation, and it's surprising that there isn't a clear and precise way to achieve this result. Thanks to everyone.

1 Reply

SirAlyonOP•10mo ago

This filter is NOT working...

Filter::make('lunivid_serie')
                    //->baseQuery(fn (Builder $query) => $query)
                    ->form([
                        Select::make('lunivid_serie')
                            ->label('Filtra per Serie')
                            ->searchable()
                            ->getSearchResultsUsing(fn (string $search): array => KiidMailReport::withoutGlobalScopes()->where('lunivid_serie', 'like', "%{$search}%")->limit(50)->pluck('lunivid_serie', 'lunivid_serie')->toArray())
                            ->getOptionLabelsUsing(fn (array $values): array => KiidMailReport::withoutGlobalScopes()->whereIn('lunivid_serie', $values)->pluck('lunivid_serie', 'lunivid_serie')->toArray()),
                    ])
                    ->query(function (Builder $query, array $data): Builder {
                        //dd($query);
                        //dd($query, $query->withoutGlobalScopes(), $data);

                        return $query
                            ->when(
                                $data['lunivid_serie'],
                                function (Builder $query, $id): Builder {

                                    //dd($query->withoutGlobalScopes()->where('lunivid_serie', '=', $id), $id);

                                    return $query->withoutGlobalScopes()->where('lunivid_serie', '=', $id);
                                },
                            );
                    }),

Filter::make('lunivid_serie')
                    //->baseQuery(fn (Builder $query) => $query)
                    ->form([
                        Select::make('lunivid_serie')
                            ->label('Filtra per Serie')
                            ->searchable()
                            ->getSearchResultsUsing(fn (string $search): array => KiidMailReport::withoutGlobalScopes()->where('lunivid_serie', 'like', "%{$search}%")->limit(50)->pluck('lunivid_serie', 'lunivid_serie')->toArray())
                            ->getOptionLabelsUsing(fn (array $values): array => KiidMailReport::withoutGlobalScopes()->whereIn('lunivid_serie', $values)->pluck('lunivid_serie', 'lunivid_serie')->toArray()),
                    ])
                    ->query(function (Builder $query, array $data): Builder {
                        //dd($query);
                        //dd($query, $query->withoutGlobalScopes(), $data);

                        return $query
                            ->when(
                                $data['lunivid_serie'],
                                function (Builder $query, $id): Builder {

                                    //dd($query->withoutGlobalScopes()->where('lunivid_serie', '=', $id), $id);

                                    return $query->withoutGlobalScopes()->where('lunivid_serie', '=', $id);
                                },
                            );
                    }),

Currently, this allows me to see the correct data for the latest 'series' when loading the table. However, as soon as I apply the filter, I don't receive any results. (The queries for the select dropdown options work correctly). The dd() inside the ->when() method, i can't see nothing wrong here(?):

Illuminate\Database\Eloquent\Builder {#3869 ▼ // app/Filament/Resources/KiidMailReportResource.php:241
  #query: 
Illuminate\Database\Query
\
Builder {#4166 ▼
    +connection: 
Illuminate\Database
\
MySqlConnection {#1827 …25}
    +grammar: 
Illuminate\Database\Query\Grammars
\
MySqlGrammar {#1828 …5}
    +processor: 
Illuminate\Database\Query\Processors
\
MySqlProcessor {#1829}
    +bindings: array:9 [▶]
    +aggregate: null
    +columns: null
    +distinct: false
    +from: "kiid_mail_report_d"
    +indexHint: null
    +joins: null
    +wheres: array:1 [▼
      0 => array:5 [▼
        "type" => "Basic"
        "column" => "lunivid_serie"
        "operator" => "="
        "value" => "41"
        "boolean" => "and"
      ]
    ]
    +groups: null
    +havings: null
    +orders: null
    +limit: null
    +offset: null
    +unions: null
    +unionLimit: null
    +unionOffset: null
    +unionOrders: null
    +lock: null
    +beforeQueryCallbacks: []
    +operators: array:33 [▶]
    +bitwiseOperators: array:6 [▶]
    +useWritePdo: false
  }
  #model: 
App\Models
\
KiidMailReport {#4079 ▶}
  #eagerLoad: []
  #localMacros: []
  #onDelete: null
  #propertyPassthru: array:1 [▶]
  #passthru: array:29 [▶]
  #scopes: []
  #removedScopes: array:1 [▼
    0 => "App\Models\Scopes\LastSerieScope"
  ]
}
"41" // app/Filament/Resources/KiidMailReportResource.php:241

Illuminate\Database\Eloquent\Builder {#3869 ▼ // app/Filament/Resources/KiidMailReportResource.php:241
  #query: 
Illuminate\Database\Query
\
Builder {#4166 ▼
    +connection: 
Illuminate\Database
\
MySqlConnection {#1827 …25}
    +grammar: 
Illuminate\Database\Query\Grammars
\
MySqlGrammar {#1828 …5}
    +processor: 
Illuminate\Database\Query\Processors
\
MySqlProcessor {#1829}
    +bindings: array:9 [▶]
    +aggregate: null
    +columns: null
    +distinct: false
    +from: "kiid_mail_report_d"
    +indexHint: null
    +joins: null
    +wheres: array:1 [▼
      0 => array:5 [▼
        "type" => "Basic"
        "column" => "lunivid_serie"
        "operator" => "="
        "value" => "41"
        "boolean" => "and"
      ]
    ]
    +groups: null
    +havings: null
    +orders: null
    +limit: null
    +offset: null
    +unions: null
    +unionLimit: null
    +unionOffset: null
    +unionOrders: null
    +lock: null
    +beforeQueryCallbacks: []
    +operators: array:33 [▶]
    +bitwiseOperators: array:6 [▶]
    +useWritePdo: false
  }
  #model: 
App\Models
\
KiidMailReport {#4079 ▶}
  #eagerLoad: []
  #localMacros: []
  #onDelete: null
  #propertyPassthru: array:1 [▶]
  #passthru: array:29 [▶]
  #scopes: []
  #removedScopes: array:1 [▼
    0 => "App\Models\Scopes\LastSerieScope"
  ]
}
"41" // app/Filament/Resources/KiidMailReportResource.php:241

Anyone? 😦 Pump! 🙂 Anyone? Please 🙂

Gaming

Programming

Custom Filter and Query Scope