Filament•15mo ago

chart with laravel-trend throwing SQL error

I've followed this https://filamentphp.com/docs/3.x/widgets/charts#generating-chart-data-from-an-eloquent-model and use "trend", filament recommended for generating data. But it gives me below error

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db.visitors.created_at' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (Connection: mysql, SQL: select date_format(created_at, '%Y-%m') as date, count(*) as aggregate from `visitors` where `created_at` between 2023-01-01 00:00:00 and 2023-12-31 23:59:59 group by `date` order by `date` asc)

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db.visitors.created_at' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (Connection: mysql, SQL: select date_format(created_at, '%Y-%m') as date, count(*) as aggregate from `visitors` where `created_at` between 2023-01-01 00:00:00 and 2023-12-31 23:59:59 group by `date` order by `date` asc)

My chart data

protected function getData(): array
{
    $data = Trend::model(Visitor::class)
        ->between(
            start: now()->startOfYear(),
            end: now()->endOfYear(),
        )
        ->perMonth()
        ->count();

    return [
        'datasets' => [
            [
                'label' => 'Visitors',
                'data' => $data->map(fn (TrendValue $value) => $value->aggregate),
            ],
        ],
        'labels' => $data->map(fn (TrendValue $value) => $value->date),
    ];
}

protected function getData(): array
{
    $data = Trend::model(Visitor::class)
        ->between(
            start: now()->startOfYear(),
            end: now()->endOfYear(),
        )
        ->perMonth()
        ->count();

    return [
        'datasets' => [
            [
                'label' => 'Visitors',
                'data' => $data->map(fn (TrendValue $value) => $value->aggregate),
            ],
        ],
        'labels' => $data->map(fn (TrendValue $value) => $value->date),
    ];
}

And my database is

$table->id();
$table->string('fingerprints');
$table->date('date');
$table->timestamps();

$table->id();
$table->string('fingerprints');
$table->date('date');
$table->timestamps();

If I put strict => false from config/database.php then error is gone, but it showing wrong data in chart. what should be the problem?

1 Reply

VpOP•15mo ago

Ah, found the problem.. because I have another 'date' column, trend query like date_format(created_at, '%Y-%m') as date

Gaming

Programming

chart with laravel-trend throwing SQL error