Apollos
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'),
]);
}
}3 replies