Laravel selectRaw聚合列报错：total_quantity列未找到（Filament场景）

在Filament开发畅销商品展示表格时，使用selectRaw('product_id, sum(quantity) as total_quantity')做聚合查询，触发了Column not found错误。报错SQL里看不到定义的total_quantity别名，但排序语句却在引用它。

问题代码 #

public function table(Table $table): Table
{
    return $table
        ->query(
            OrderDetails::query()
                ->selectRaw('product_id, sum(quantity) as total_quantity')
                ->groupBy('product_id')
                ->orderBy('total_quantity', 'desc')
        )
        ->columns([
            Tables\Columns\TextColumn::make('product.name'),
            Tables\Columns\TextColumn::make('total_quantity'),
        ]);
}

报错信息 #

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'total_quantity' in 'order clause'

select
  count(*) as aggregate
from
  `order_details`
group by
  `product_id`
order by
  total_quantity,
  `order_details`.`id` asc

问题原因 #

Filament表格会自动执行两次查询：一次获取列表数据，一次做分页统计（count(*)）。你的selectRaw只在列表查询中生效，但分页统计查询时被Filament重置了，导致统计SQL里没有total_quantity这个别名，但排序逻辑还在引用它，所以报错。

解决方法 #

方法1：改用orderByRaw直接写排序表达式

把orderBy('total_quantity', 'desc')替换成orderByRaw('sum(quantity) desc')，这样分页统计查询也能识别排序逻辑：

public function table(Table $table): Table
{
    return $table
        ->query(
            OrderDetails::query()
                ->selectRaw('product_id, sum(quantity) as total_quantity')
                ->groupBy('product_id')
                ->orderByRaw('sum(quantity) desc')
        )
        ->columns([
            Tables\Columns\TextColumn::make('product.name'),
            Tables\Columns\TextColumn::make('total_quantity'),
        ]);
}

方法2：自定义分页统计查询

给表格指定countsQuery，确保统计查询逻辑和主查询匹配：

public function table(Table $table): Table
{
    return $table
        ->query(
            OrderDetails::query()
                ->selectRaw('product_id, sum(quantity) as total_quantity')
                ->groupBy('product_id')
                ->orderBy('total_quantity', 'desc')
        )
        ->countsQuery(fn ($query) => $query
            ->selectRaw('count(distinct product_id) as aggregate')
            ->groupBy('product_id'))
        ->columns([
            Tables\Columns\TextColumn::make('product.name'),
            Tables\Columns\TextColumn::make('total_quantity'),
        ]);
}

内容的提问来源于stack exchange，提问作者Kemal Özvarol