Can not use selectRaw element on where clause-CodePudding

Trying to use where clause on "selectRaw" object that I defined with 'as'.

But it doesnt work.

        $rafbul = Inventory::selectRaw('*, sum(quantity) as quantity_sum')
        ->join('shelves', function($q){
          $q->on('shelves.id', 'inventories.shelf_id');
          $q->where('type', '!=', 'stock');
        })
        ->where('product_id', $product_id)
        ->where('quantity', '>', 0)
        ->groupBy('shelf_id')
        ->orderBy('inventories.created_at')
        ->first();

It returns;

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'quantity_sum' in 'where clause' (SQL: select *, sum(quantity) as quantity_sum from `inventories` inner join `shelves` on `shelves`.`id` = `inventories`.`shelf_id` and `type` != stock where `product_id` = 6188 and `quantity_sum` > 0 group by `shelf_id` order by `inventories`.`created_at` asc limit 1)

Whats wrong with that logic?

CodePudding user response：

To filter by an aggregate column, the having()/havingRaw() methods should be used

$rafbul = Inventory::selectRaw('*, sum(quantity) as quantity_sum')
    ->join('shelves', function($q){
        $q->on('shelves.id', 'inventories.shelf_id');
        $q->where('type', '!=', 'stock');
    })
    ->where('product_id', $product_id)
    ->groupBy('shelf_id')
    ->havingRaw('sum(quantity) > ?', [0])
    ->orderBy('inventories.created_at')
    ->first();