Question 281663 in Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel selectRaw summed column not found

I’m trying to make a custom table widget in filament for showing best selling products. When I do sum(quantity) as total_quantity, it gives me Column not found error. Here’s my entire code and the error message :

    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([
                TablesColumnsTextColumn::make('product.name'),
                TablesColumnsTextColumn::make('total_quantity'),
            ]);
    }

Error message with the compiled SQL :

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

The weird thing is, as you can see, the compiled SQL code doesn’t have sum(quantity) as total_quantity in it.

Tags:

2

Answers


  1. 0

    You can use the orderByRaw method and reference the column alias directly in the raw SQL for ordering.

    public function table(Table $table): Table
{
    return $table
        ->query(
            OrderDetails::query()
                ->selectRaw('product_id, sum(quantity) as total_quantity')
                ->groupBy('product_id')
                ->orderByRaw('total_quantity DESC') // Use orderByRaw to reference the alias
        )
        ->columns([
            TablesColumnsTextColumn::make('product.name'),
            TablesColumnsTextColumn::make('total_quantity'),
        ]);
}
    Login or Signup to reply.
  2. 0

    As I mentioned in a comment, total_quatity column isn’t available to sort the query, you’ll need to perform a subquery to achieve your desired result.

    You can try this instead:

    public function table(Table $table): Table
{
    $subQuery = OrderDetails::query()
        ->selectRaw('product_id, sum(quantity) as total_quantity')
        ->groupBy('product_id')
        ->orderByRaw('sum(quantity) desc');

    return $table
        ->query(
            DB::query()->fromSub($subQuery, 'sub')
        )
        ->columns([
            TablesColumnsTextColumn::make('product.name'),
            TablesColumnsTextColumn::make('total_quantity'),
        ]);
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search