F
Filament•4mo ago
moinmichi

Searchable column with scoped model

I've got a table on a custom page which uses a model with a scope for its query. This is the scope: 
public function scopeCalculatedClassFrequencies(Builder $query, string|null $name = null): void
    {
        $query->select(
            $this->getTable() . '.id',
            DB::raw('"' . __('Zügigkeiten') . '" as name'),
            DB::raw('LEFT(j.titel, 4) as year'),
            DB::raw('SUM(cf.zuegigkeit) AS calculated_value'),
            $this->getTable() . '..number AS expected_value',
        )
            ->where('reference', 'class-frequencies')
            ->join(app(Year::class)->getTable() . ' as j', DB::raw('LEFT(j.titel, 4)'), '=',  $this->getTable() . '.year')
            ->join(app(ClassFrequency::class)->getTable() . ' as cf', 'cf.jahrgang', '=', 'j.id')
            ->groupBy(DB::raw('LEFT(j.titel, 4)'))
            ->orderBy('year')
    }
public function scopeCalculatedClassFrequencies(Builder $query, string|null $name = null): void
    {
        $query->select(
            $this->getTable() . '.id',
            DB::raw('"' . __('Zügigkeiten') . '" as name'),
            DB::raw('LEFT(j.titel, 4) as year'),
            DB::raw('SUM(cf.zuegigkeit) AS calculated_value'),
            $this->getTable() . '..number AS expected_value',
        )
            ->where('reference', 'class-frequencies')
            ->join(app(Year::class)->getTable() . ' as j', DB::raw('LEFT(j.titel, 4)'), '=',  $this->getTable() . '.year')
            ->join(app(ClassFrequency::class)->getTable() . ' as cf', 'cf.jahrgang', '=', 'j.id')
            ->groupBy(DB::raw('LEFT(j.titel, 4)'))
            ->orderBy('year')
    }
The table uses this scope as the query and a searchable name column. Because of the scope I use a callback function to add the search condition to the query: 
$table->query(ImportExpectation::CalculatedClassFrequencies())
->columns([
                TextColumn::make('name')
                    ->searchable(isGlobal: false, isIndividual: true, query: function(Builder $query, string $search) {
                        $query->having('name', 'like', '%' . $search . '%');
                }),
$table->query(ImportExpectation::CalculatedClassFrequencies())
->columns([
                TextColumn::make('name')
                    ->searchable(isGlobal: false, isIndividual: true, query: function(Builder $query, string $search) {
                        $query->having('name', 'like', '%' . $search . '%');
                }),
Unfortunately the search doesn't apply to the data. The table shows that a filter is active with the search string, but still all data is shown. Any idea what I'm doing wrong?
1 Reply
moinmichi
moinmichiOP•4mo ago
if I dump the $query->toRawSql() in the searchable callback function, I get 
select * from `import_expectations`
select * from `import_expectations`
but if I dump $table->getQuery()->toRawSql() I get 
select `import_expectations`.`id`, "Zügigkeiten" as name, LEFT(j.titel, 4) as year, SUM(cf.zuegigkeit) AS calculated_value, `import_expectations`.``.`number` as `expected_value`, ABS(SUM(cf.zuegigkeit) - import_expectations.number) AS difference from `import_expectations` inner join `se_jahrgaenge` as `j` on LEFT(j.titel, 4) = `import_expectations`.`year` inner join `se_klassenstufen` as `cf` on `cf`.`jahrgang` = `j`.`id` where `reference` = 'class-frequencies' group by LEFT(j.titel, 4) order by `year` asc
select `import_expectations`.`id`, "Zügigkeiten" as name, LEFT(j.titel, 4) as year, SUM(cf.zuegigkeit) AS calculated_value, `import_expectations`.``.`number` as `expected_value`, ABS(SUM(cf.zuegigkeit) - import_expectations.number) AS difference from `import_expectations` inner join `se_jahrgaenge` as `j` on LEFT(j.titel, 4) = `import_expectations`.`year` inner join `se_klassenstufen` as `cf` on `cf`.`jahrgang` = `j`.`id` where `reference` = 'class-frequencies' group by LEFT(j.titel, 4) order by `year` asc
That's weird, why doesn't match the $query in the callback function the query of the table? Has anyone an idea?

Did you find this page helpful?