Filament•2mo ago

Sorting ignores relation condition

Hi, I have been trying to get this to work but im stuck. Basically i got a relation on my record called 'animals' and on the table i need to have several counts of this object with different conditions, like so:

                Tables\Columns\TextColumn::make('active_animals')
                    ->getStateUsing(fn (Family $record): string => $record->animals()->where('active', true)->count())
                    ->sortable(query: function (Builder $query, string $direction): Builder {
                        return $query
                            ->orderBy('animals_count', $direction);
                    })->label('Aantal actieve dieren'),

                Tables\Columns\TextColumn::make('active_animals')
                    ->getStateUsing(fn (Family $record): string => $record->animals()->where('active', true)->count())
                    ->sortable(query: function (Builder $query, string $direction): Builder {
                        return $query
                            ->orderBy('animals_count', $direction);
                    })->label('Aantal actieve dieren'),

Initially i had a custom attribute:

    public function getActiveAnimalsCountAttribute(): string
    {
        return $this->animals()->where('active', true)->count();
    }

    public function getActiveAnimalsCountAttribute(): string
    {
        return $this->animals()->where('active', true)->count();
    }

But i was unable to sort on this. Then i figured maybe im able to push this logic to the database and add a virtual column with the count where active is true. But couldn't find any resource on how to do this. So now i tried the sortable function but somehow when i sort it orders by the actual 'animals_count' (makes sense given the function). But this discards the "where animal is active". Basically now im stuck and have not really an idea how to move forward... Anyone has experience with this of has some ideas?

7 Replies

CodeWithDennis•2mo ago

@Augus van GIls So basically you trying to count active animals from the family model?

TextColumn::make('animals_count')->counts([
    'animals' => fn (Builder $query) => $query->where('active', true),
])

TextColumn::make('animals_count')->counts([
    'animals' => fn (Builder $query) => $query->where('active', true),
])

CodeWithDennis•2mo ago

Hopefully that helps, here are the docs. - https://filamentphp.com/docs/3.x/tables/columns/relationships#counting-relationships @Augus van GIls 🙂

Column relationships - Table Builder - Filament

Augus•2mo ago

@CodeWithDennis that solution makes it that im unable to add multiple counts on the same table

                Tables\Columns\TextColumn::make('animals_count')
                    ->counts([
                        'animals' => fn (Builder $query): Builder => $query->where('active', true),
                    ])
                    ->sortable()
                    ->label('Aantal actieve dieren'),
                Tables\Columns\TextColumn::make('animals_count')
                    ->counts([
                        'animals' => fn (Builder $query): Builder => $query->where([
                            'active' => true,
                            'castrated' => false,
                            'gender' => 'M',
                        ]),
                    ])
                    ->sortable()
                    ->label('Aantal actieve dieren excl. hamel'),

                Tables\Columns\TextColumn::make('animals_count')
                    ->counts([
                        'animals' => fn (Builder $query): Builder => $query->where('active', true),
                    ])
                    ->sortable()
                    ->label('Aantal actieve dieren'),
                Tables\Columns\TextColumn::make('animals_count')
                    ->counts([
                        'animals' => fn (Builder $query): Builder => $query->where([
                            'active' => true,
                            'castrated' => false,
                            'gender' => 'M',
                        ]),
                    ])
                    ->sortable()
                    ->label('Aantal actieve dieren excl. hamel'),

You must name the column 'animals_count' but in FIlament you can't have two columns having the same name This made me think, i am able to add another scoped relation in my model: Model:

    public function active_animals()
    {
        return $this->animals()->where('active', true);
    }

    public function active_animals()
    {
        return $this->animals()->where('active', true);
    }

Table:

                Tables\Columns\TextColumn::make('active_animals_count')
                    ->counts('active_animals')
                    ->sortable()
                    ->label('Aantal actieve dieren'),

                Tables\Columns\TextColumn::make('active_animals_count')
                    ->counts('active_animals')
                    ->sortable()
                    ->label('Aantal actieve dieren'),

It's not really an elegant solution but so far it does work

CodeWithDennis•2mo ago

You don't have to give them the same name i think. @Augus van GIls Also, you don't have to add scopes. But its possible.

Augus•2mo ago

TextColumn::make('animals_count')->counts([
    'animals' => fn (Builder $query) => $query->where('active', true),
])

TextColumn::make('animals_count')->counts([
    'animals' => fn (Builder $query) => $query->where('active', true),
])

This one needs to have the 'animals_count' name per documentation @CodeWithDennis

In this example, users is the name of the relationship to count from. The name of the column must be users_count, as this is the convention that Laravel uses for storing the result.

In this example, users is the name of the relationship to count from. The name of the column must be users_count, as this is the convention that Laravel uses for storing the result.

https://laravel.com/docs/11.x/eloquent-relationships#counting-related-models

CodeWithDennis•2mo ago

Ah 😅 You got it working now?

Augus•2mo ago

Yea with a function withing my Model:

    public function active_not_castrated_animals()
    {
        return $this->animals()->where([
            'active' => true,
            'castrated' => false,
        ]);
    }

    public function active_not_castrated_animals()
    {
        return $this->animals()->where([
            'active' => true,
            'castrated' => false,
        ]);
    }

And this as a field:

                Tables\Columns\TextColumn::make('active_not_castrated_animals_count')
                    ->counts('active_not_castrated_animals')
                    ->sortable()
                    ->label('Aantal actieve dieren (excl. hamel)'),

                Tables\Columns\TextColumn::make('active_not_castrated_animals_count')
                    ->counts('active_not_castrated_animals')
                    ->sortable()
                    ->label('Aantal actieve dieren (excl. hamel)'),

Not sure if this is the proper way to go but it works But this way i can have proper multiple count tables :

                Tables\Columns\TextColumn::make('active_not_castrated_animals_count')
                    ->counts('active_not_castrated_animals')
                    ->sortable()
                    ->label('Aantal actieve dieren (excl. hamel)'),
                Tables\Columns\TextColumn::make('animals_count')
                    ->label('Totaal aantal dieren')
                    ->sortable()
                    ->counts('animals'),

                Tables\Columns\TextColumn::make('active_not_castrated_animals_count')
                    ->counts('active_not_castrated_animals')
                    ->sortable()
                    ->label('Aantal actieve dieren (excl. hamel)'),
                Tables\Columns\TextColumn::make('animals_count')
                    ->label('Totaal aantal dieren')
                    ->sortable()
                    ->counts('animals'),

Gaming

Programming

Sorting ignores relation condition