#Sorting ignores relation condition

21 messages · Page 1 of 1 (latest)

robust rain
#

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'),

Initially i had a custom attribute:

    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?

azure nacelle
#

@robust rain 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),
])
robust rain
#

@azure nacelle 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'),
#

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);
    }

Table:

                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

azure nacelle
#

You don't have to give them the same name i think. @robust rain

#

Also, you don't have to add scopes. But its possible.

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

This one needs to have the 'animals_count' name

#

per documentation @azure nacelle

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.
azure nacelle
#

Ah 😅

#

You got it working now?

robust rain
#

Yea with a function withing my Model:

# 
    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)'),
#

Not sure if this is the proper way to go but it works