Filament•2y ago

Make a graph to count records by month and year

Hi everyone, I have a Device model with a installation_date attribute. I'd like to build a line chart in a widget which shows how many devices have been installed per month. Some months will have 0 installations but they still have to be displayed in the graph. Also, I'd like to show the year on the months-axis (like 03-2023).
Has anyone an idea how to achieve that? Thanks!

2 Replies

Thijmen•2y ago

Look at the #apex-charts plugin. Some code was already shared in there. You could also try the original charts: https://filamentphp.com/docs/2.x/admin/dashboard/charts

Filament

Charts - Dashboard - Admin Panel - Filament

The elegant TALL stack admin panel for Laravel artisans.

SpârkyOP•2y ago

Thanks! I forgot to put some code I tried 😅

protected function getData(): array
    {
        $installations = Device::select(DB::raw('COUNT(*) as total'), DB::raw('MONTH(installation_date) as month'), DB::raw('YEAR(installation_date) as year'))
            ->having('month', 'not like', 'null')
            ->having('year', 'not like', 'null')
            ->groupBy(DB::raw('MONTH(installation_date)'), DB::raw('YEAR(installation_date)'))
            ->orderBy(DB::raw('YEAR(installation_date)'), 'asc')
            ->orderBy(DB::raw('MONTH(installation_date)'), 'asc')
            ->get();
        return [
            'datasets' => [
                [
                    'label' => 'Appareils installés',
                    'data' => array_map(fn($installation) => $installation['total'], $installations->toArray()),
                ],
            ],
            'labels' => array_map(fn($installation) => $installation['month'].'-'.$installation['year'], $installations->toArray()),
        ];
    }

protected function getData(): array
    {
        $installations = Device::select(DB::raw('COUNT(*) as total'), DB::raw('MONTH(installation_date) as month'), DB::raw('YEAR(installation_date) as year'))
            ->having('month', 'not like', 'null')
            ->having('year', 'not like', 'null')
            ->groupBy(DB::raw('MONTH(installation_date)'), DB::raw('YEAR(installation_date)'))
            ->orderBy(DB::raw('YEAR(installation_date)'), 'asc')
            ->orderBy(DB::raw('MONTH(installation_date)'), 'asc')
            ->get();
        return [
            'datasets' => [
                [
                    'label' => 'Appareils installés',
                    'data' => array_map(fn($installation) => $installation['total'], $installations->toArray()),
                ],
            ],
            'labels' => array_map(fn($installation) => $installation['month'].'-'.$installation['year'], $installations->toArray()),
        ];
    }

This code gives me this, which is pretty close to what I want but I don't have any clue on how to generate the other months on the axis

Gaming

Programming

Make a graph to count records by month and year