[2.x] Implementing withCount and allowing sortable

[lvismer]

If one includes a withCount it throws an error on the query as the Component includes normal columns as part of the select even if a query builder is used, ie.

class Table extends DataTableComponent
{
    public function configure(): void
    {
        $this->setPrimaryKey('id');
    }

    public function columns(): array
    {
        return [
            Column::make(__('Name'), 'name')
                ->sortable()
                ->searchable(),
            Column::make(__('Users'), 'users_count')
                ->sortable()
        ];
    }

    public function builder(): Builder
    {
        return Client::query()
            ->select('id', 'name')
            ->withCount('users');
    }
}

The resulting query which throws an error from the above implementation is:

select `id`, 
	`name`, 
	(select count(*) from `users` where `clients`.`id` = `users`.`client_id` and `users`.`deleted_at` is null) as `users_count`, 
	`clients`.`name` as `name`, 
	`clients`.`users_count` as `users_count` 
	from `clients` 
	where `clients`.`deleted_at` is null

It is possible to circumvent the issue above by using a label in the columns() function but then the column is not sortable. It would be nice to have a sortable withCount column.

The following works with just a label, but without sorting.

    public function columns(): array
    {
        return [
            Column::make(__('Name'), 'name')
                ->sortable()
                ->searchable(),
            Column::make(__('Users'), 'users_count')
                ->label(fn($row) => $row->users_count)
                ->sortable()
        ];
    }

Has anyone got a working withCount example that is sortable.

Many thanks

[satoved]

Got the same problem when trying to convert from V1. Worked on V1, but not here.

[AlbertMN]

Got the same problem :)
I saw @rappasoft commenting;

This is the intended functionality.
(#850 (comment))

Does this mean it's not something that is planned on being fixed?

[lrljoe]

It's a bit of a hack, but you can sort on a count as follows, you just don't reference the count field in the second reference, use anything that actually exists, but that you preferably aren't using elsewhere.

Column::make('Column Name', 'id')
    ->format(
        fn ($value, $row, Column $column) => intval($row->**count_field_here**)
    )
    ->sortable(fn (Builder $query, string $direction) => $query->orderBy('**count_field_here**', $direction)),

[jornatf]

It doesn't completely solve the problem because if we sort the table on the view, it sorts according to the ID, not according to the Count...

[lrljoe]

Are you using Tailwind or Bootstrap?

Might just be my branch but if I add column, and reference a relationship on the model called "findings", and including that in my Builder function using withCount('findings') then it does work as expected (for me).

Column::make('Findings', 'id')
->format(
    fn ($value, $row, Column $column) => intval($row->findings_count)
)
->sortable(fn (Builder $query, string $direction) => $query->orderBy('findings_count', $direction)),

And I sort on the column, it correctly returns the following at the tail end of the query, which I'd expect:

 order by `findings_count` asc limit 10 offset 0

Clicking the header again returns

 order by `findings_count` desc limit 10 offset 0

If I then click the header again, it removes the order (as expected)

Can you let know what you're seeing in terms of the query, or link me to your DataTable component and the underlying models and I'll see if I can replicate it.

[ChrisThompsonTLDR]

@lrljoe The issue with your code is that

Column::make('Findings', 'id')

will update the address bar with ?table-sorts[id]=desc which, if you're including the id in the columns already, will make sorting on the count column impossible.

If we change Column::make('Findings', 'findings_count') to Column::make('Findings', 'id') then we get the error mentioned by the OP. The second parm of make() must be a valid database column, and *_count columns don't count.

Or, in my case, I want to have three columns based on counts. Sorting no longer works because all three columns are trying to sort on id.

            Column::make('Spaces', 'id')
                ->format(
                    fn ($value, $row, Column $column) => intval($row->spaces_count)
                )
                ->sortable(fn (Builder $query, string $direction) => $query->orderBy('spaces_count', $direction)),
            Column::make('Comments', 'id')
                ->format(
                    fn ($value, $row, Column $column) => intval($row->comments_count)
                )
                ->sortable(fn (Builder $query, string $direction) => $query->orderBy('comments_count', $direction)),
            Column::make('Media', 'id')
                ->format(
                    fn ($value, $row, Column $column) => intval($row->media_count)
                )
                ->sortable(fn (Builder $query, string $direction) => $query->orderBy('media_count', $direction)),

[lrljoe]

@ChrisThompsonTLDR

Sorting on label columns works these days (my response is quite old), which mitigates the issue as you don't need to define the field in the make() for the column.

So don't define a second parameter to make() and use label() instead of format(), and it'll work smoothly