Filament Table Filtering: Summarizing Data by Year in Separate Rows

I want to apply a filter to my Filament table. My goal is to create a separate row for each year as a result of the filtering, and summarize the data for that year in each row.
For example, I want to obtain a result like this after filtering:

year | sales_count | total_amount | date_of_sales      | order_ids
2024 | 123         | 18284.00     | 2024-10-10 00:00:00| 28,29,34,38,39,...,180
2023 | 85          | 12500.00     | 2023-11-15 00:00:00| 1,2,3,4,5,...,90
2022 | 102         | 15000.00     | 2022-09-20 00:00:00| 91,92,93,94,...,195

year | sales_count | total_amount | date_of_sales      | order_ids
2024 | 123         | 18284.00     | 2024-10-10 00:00:00| 28,29,34,38,39,...,180
2023 | 85          | 12500.00     | 2023-11-15 00:00:00| 1,2,3,4,5,...,90
2022 | 102         | 15000.00     | 2022-09-20 00:00:00| 91,92,93,94,...,195

For filtering, I'm using the following code:
php

Tables\Filters\SelectFilter::make('week')->options([
    'Saatlik' => 'Saatlik',
    'Günlük' => 'Günlük', 
    'Haftalık' => 'Haftalık',
    'Aylık' => 'Aylık',
    'Yıllık' => 'Yıllık',
])->query(function (Builder $query, array $data): Builder {
    $type = $data['value'];

    if ($type === "Yıllık") {
        $query->selectRaw('
            YEAR(date_of_sales) as year,
            COUNT(*) as sales_count,
            SUM(order_total_amount) as total_amount,
            MAX(date_of_sales) as date_of_sales,
            GROUP_CONCAT(id) as order_ids
        ')
        ->whereNull('deleted_at')
        ->whereNotNull('date_of_sales')
        ->groupBy(DB::raw('YEAR(date_of_sales)'))
        ->orderBy(DB::raw('YEAR(date_of_sales)'), 'desc');
    }

    return $query;
}),

Tables\Filters\SelectFilter::make('week')->options([
    'Saatlik' => 'Saatlik',
    'Günlük' => 'Günlük', 
    'Haftalık' => 'Haftalık',
    'Aylık' => 'Aylık',
    'Yıllık' => 'Yıllık',
])->query(function (Builder $query, array $data): Builder {
    $type = $data['value'];

    if ($type === "Yıllık") {
        $query->selectRaw('
            YEAR(date_of_sales) as year,
            COUNT(*) as sales_count,
            SUM(order_total_amount) as total_amount,
            MAX(date_of_sales) as date_of_sales,
            GROUP_CONCAT(id) as order_ids
        ')
        ->whereNull('deleted_at')
        ->whereNotNull('date_of_sales')
        ->groupBy(DB::raw('YEAR(date_of_sales)'))
        ->orderBy(DB::raw('YEAR(date_of_sales)'), 'desc');
    }

    return $query;
}),

However, this code doesn't give me the desired result. All records are returned as a result of the filtering, and a separate row is not created for each year.
My questions are:

What changes do I need to make in the filtering code?
What steps should I follow to create a separate row for each year and summarize the data for that year?
How can I display the data obtained from the query result in the Filament table?

Thank you in advance for your help.
Best regards.

message.txt14.37KB

Filament•15mo ago

Mehmet K.

Filament Table Filtering: Summarizing Data by Year in Separate Rows

year | sales_count | total_amount | date_of_sales      | order_ids
2024 | 123         | 18284.00     | 2024-10-10 00:00:00| 28,29,34,38,39,...,180
2023 | 85          | 12500.00     | 2023-11-15 00:00:00| 1,2,3,4,5,...,90
2022 | 102         | 15000.00     | 2022-09-20 00:00:00| 91,92,93,94,...,195

year | sales_count | total_amount | date_of_sales      | order_ids
2024 | 123         | 18284.00     | 2024-10-10 00:00:00| 28,29,34,38,39,...,180
2023 | 85          | 12500.00     | 2023-11-15 00:00:00| 1,2,3,4,5,...,90
2022 | 102         | 15000.00     | 2022-09-20 00:00:00| 91,92,93,94,...,195

For filtering, I'm using the following code:
php

Tables\Filters\SelectFilter::make('week')->options([
    'Saatlik' => 'Saatlik',
    'Günlük' => 'Günlük', 
    'Haftalık' => 'Haftalık',
    'Aylık' => 'Aylık',
    'Yıllık' => 'Yıllık',
])->query(function (Builder $query, array $data): Builder {
    $type = $data['value'];

    if ($type === "Yıllık") {
        $query->selectRaw('
            YEAR(date_of_sales) as year,
            COUNT(*) as sales_count,
            SUM(order_total_amount) as total_amount,
            MAX(date_of_sales) as date_of_sales,
            GROUP_CONCAT(id) as order_ids
        ')
        ->whereNull('deleted_at')
        ->whereNotNull('date_of_sales')
        ->groupBy(DB::raw('YEAR(date_of_sales)'))
        ->orderBy(DB::raw('YEAR(date_of_sales)'), 'desc');
    }

    return $query;
}),

Tables\Filters\SelectFilter::make('week')->options([
    'Saatlik' => 'Saatlik',
    'Günlük' => 'Günlük', 
    'Haftalık' => 'Haftalık',
    'Aylık' => 'Aylık',
    'Yıllık' => 'Yıllık',
])->query(function (Builder $query, array $data): Builder {
    $type = $data['value'];

    if ($type === "Yıllık") {
        $query->selectRaw('
            YEAR(date_of_sales) as year,
            COUNT(*) as sales_count,
            SUM(order_total_amount) as total_amount,
            MAX(date_of_sales) as date_of_sales,
            GROUP_CONCAT(id) as order_ids
        ')
        ->whereNull('deleted_at')
        ->whereNotNull('date_of_sales')
        ->groupBy(DB::raw('YEAR(date_of_sales)'))
        ->orderBy(DB::raw('YEAR(date_of_sales)'), 'desc');
    }

    return $query;
}),

message.txt14.37KB

Filament Table Filtering: Summarizing Data by Year in Separate Rows

Filament Table Filtering: Summarizing Data by Year in Separate Rows

Similar Threads

Similar Threads

Similar Threads