F
Filament•3mo ago
Augus

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
CodeWithDennis•3mo 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
CodeWithDennis•3mo ago
Augus
AugusOP•3mo 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
CodeWithDennis•3mo 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
AugusOP•3mo 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
CodeWithDennis•3mo ago
Ah 😅 You got it working now?
Augus
AugusOP•3mo 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'),
Want results from more Discord servers?
Add your server