Filament•16mo ago

Getting wrong data for `count(*)` field 😢

For a table query, I call select() to specify a handful of non-standard "fields". One of them is specified like this: DB::raw('count(*) as current_result') Then, I define the column like so: Tables\Columns\TextColumn::make('current_result') The column appears, but every value is 1. I'm using laravel debugbar and I can copy the query that is executed and it generates a bunch of results for current_result that are not 1. Do I have to do something special to set up a column for such data...? I feel like I'm missing something, but I can't figure it out. 😳

2 Replies

devfaysal•16mo ago

Post everything in detail. If you do now show the code you have written, nobody will understand what is the problem you are facing. In case you want to show the count of relationship data, then you can just use the count() method like this: TextColumn::make('students_count')->counts('students'),

TravisOP•16mo ago

It may not be easy to post everything in detail, but I can share a few points fairly easily. First, I have an eloquent model called Genage and I pass the following query to my table's query() method:

Genage::select(
                'genages_id',
                'genders.genders_id',
                DB::raw('year(CURDATE()) - genage_age_to as age_from'),
                DB::raw('year(CURDATE()) - genage_age_from as age_to'),
                'genage_quota_2 as quota',
                DB::raw('count(*) as current_result'),
            )
            ->leftJoinSub(
                $this->subquery(), // <--- this returns a DB Query Builder query
                'r',
                function (JoinClause $join) {
                    $join->on('genage_gender', '=', 'r.gender')
                        ->on('genage_age_from', '<=', 'r.age_value')
                        ->on('genage_age_to', '>=', 'r.age_value');
                }
            )
            ->join('genders', 'genders.genders_id', '=', 'genages.genage_gender')
            ->where([
                ['genage_survey', '=', 16],
                ['genage_quota', '!=', 0],
            ])
            ->groupBy('genage_gender', 'genage_age_from', 'genage_age_to')
            ->orderBy('genage_gender')
            ->orderBy('genage_age_from', 'desc')

Genage::select(
                'genages_id',
                'genders.genders_id',
                DB::raw('year(CURDATE()) - genage_age_to as age_from'),
                DB::raw('year(CURDATE()) - genage_age_from as age_to'),
                'genage_quota_2 as quota',
                DB::raw('count(*) as current_result'),
            )
            ->leftJoinSub(
                $this->subquery(), // <--- this returns a DB Query Builder query
                'r',
                function (JoinClause $join) {
                    $join->on('genage_gender', '=', 'r.gender')
                        ->on('genage_age_from', '<=', 'r.age_value')
                        ->on('genage_age_to', '>=', 'r.age_value');
                }
            )
            ->join('genders', 'genders.genders_id', '=', 'genages.genage_gender')
            ->where([
                ['genage_survey', '=', 16],
                ['genage_quota', '!=', 0],
            ])
            ->groupBy('genage_gender', 'genage_age_from', 'genage_age_to')
            ->orderBy('genage_gender')
            ->orderBy('genage_age_from', 'desc')

When it runs, all of the data looks good except for current_result. However, the SQL that is generated works perfectly fine when I run it on its own in my MySQL client, TablesPlus. (This is the same from debugbar or the query log.) In TablesPlus, current_result is never 1. When run within the Laravel/Filament app, it's always 1.

Gaming

Programming

Getting wrong data for `count(*)` field 😢