Filament•2y ago

TableWidget with morphTo and groupedBy

I have a Eloquent model StatisticsClick with the following columns: - id - model_type - model_id - created_at I have defined a morphTo relationshop:

public function model(): MorphTo
{
    return $this->morphTo();
}

public function model(): MorphTo
{
    return $this->morphTo();
}

The morphTo goes (for now) only to the Product model. For this, I wanna create a widget to show the most clicked elements, with the name and the counted number of StatisticsClick entries. Something like this result: | name | count | |-----------|-------| | Product 3 | 44 | | Product 1 | 32 | | Product 2 | 18 | To get this, I would write:

\DB::query()
    ->selectRaw("products.id, products.name, COUNT(*) as count")
    ->from("statistics_clicks")
    ->where("model_type", "Shop")
    ->join("products", "products.id", "=", "statistics_clicks.model_id")
    ->groupBy("products.id")
    ->orderBy("count")

\DB::query()
    ->selectRaw("products.id, products.name, COUNT(*) as count")
    ->from("statistics_clicks")
    ->where("model_type", "Shop")
    ->join("products", "products.id", "=", "statistics_clicks.model_id")
    ->groupBy("products.id")
    ->orderBy("count")

The problem is, that a table widgets getTableQuery only accept Illuminate\Database\Eloquent\Builderas return values and no Illuminate\Database\Query\Builder. This is my starting point:

class ProductExternalClickTableWidget extends TableWidget
{
    protected int | string | array $columnSpan = "full";


    protected function getTableQuery(): Builder
    {
        return StatisticsClick::query()
            ->with("model");
    }

    protected function getTableColumns(): array
    {
        return [
            Tables\Columns\TextColumn::make("model.name")
        ];
    }
}

class ProductExternalClickTableWidget extends TableWidget
{
    protected int | string | array $columnSpan = "full";


    protected function getTableQuery(): Builder
    {
        return StatisticsClick::query()
            ->with("model");
    }

    protected function getTableColumns(): array
    {
        return [
            Tables\Columns\TextColumn::make("model.name")
        ];
    }
}

This shows me the table with a list of all clicks, by the name of the related product. But how to group now after product.id and get the count? Any ideas?

2 Replies

Patrick Boivin•2y ago

@bernhard86 Not exactly a direct answer to you question but, have you heard of Sushi? https://filamentphp.com/blog/how-to-consume-an-external-api-with-filament-tables This article explains how to use it to query an external API and present the results in a Filament table. I've also used it to "wrap" a custom DB::query() (instead of an API), to further process and group some data. Maybe this can be useful for your use-case.

Matteo De Blasis•12mo ago

I'm sorry i would need some help, because i have the same problem. I have a notes table (i have attached the structure), each note can be set to highlight. I created a relation in model Note, it goes only to Appointment for now. I need to create a Table widget for show only highlights note (i attach the widget's structure ), but the model.id columns is empty. What's wrong with what I am doing?

Gaming

Programming

TableWidget with morphTo and groupedBy