Syntax error or access violation: 1055 Expression #1 of ORDER BY clause is not in GROUP BY
Im receiving the following error when building a table widget for my dashboard. What is the correct way to accomplish this without setting only_full_group_by to false.
SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'reporting.invoices.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
SELECT
agent_name, COUNT(invoice_id) AS total_invoices FROM invoices GROUP BY agent_name ORDER BY invoices.id ASC limit 11 OFFSET 0
<?php
namespace App\Filament\Widgets;
use App\Models\Invoice;
use Filament\Tables\Columns\TextColumn;
use Filament\Tables\Table;
use Filament\Widgets\TableWidget as BaseWidget;
class AgentTable extends BaseWidget
{
public function table(Table $table): Table
{
return $table
->query(
Invoice::query()
->select('agent_name')
->selectRaw('COUNT(*) as total_invoices')
->groupBy('agent_name')
)
->columns([
TextColumn::make('agent_name')
->searchable()
->sortable(),
TextColumn::make('total_invoices')
->label('Total Invoices'),
]);
}
}1 Reply
Hey @Apollos , did you manage to find a solution for this? I have the same problem
I found a solution for my issue: The problem when grouping is that Filament expects an ID column when displaying the data in a table.
However, as we know, when we group rows according to a particular field, we don't need the ID of a row. To fix this, we need to create a new field ID that will be formed by the number of total grouped roles.
That can be done through SQL like this:
I have modified my code to adapt it to your need, but you'll have to test it.