How can (SUM) in pivot table field and searching that field in yajra-laravel-datatable package (laravel 5.6)

Question

! have three table

1. inventories enter image description here
2. warehouses enter image description here
3. inventory_has_warehouses enter image description here

I have use laravel yajra datatable. i need sum and search of starting_balance this field in inventory_has_warehouses pivot table

my code:

    $id = Auth::user()->id;

        $row = Inventory::with('contact')->with('warehouse')
        ->select(
          'inventories.*',
          DB::raw('SUM(inventory_has_warehouses.starting_balance) as total') 
        )
        ->leftJoin('inventory_has_warehouses', 'inventory_has_warehouses.inventory_id', '=', 'inventories.id')
        ->leftJoin('warehouses', 'warehouses.id', '=', 'inventory_has_warehouses.warehouse_id')
        ->where('inventories.subscriber_id',$id)
        ->groupBy('inventories.id');

        $datatable = DataTables::of($row)

        ->filterColumn('total', function($query, $keyword) {
            $query->whereRaw('sum(inventory_has_warehouses.starting_balance) like ?', ['%'.$keyword.'%']);

        })

        return $datatable->make(true);

but i fount this type of error

Exception Message:↵↵SQLSTATE[HY000]: General error: 1111 Invalid use of group function (SQL: select count() as aggregate from (select inventories., SUM(inventory_has_warehouses.starting_balance) as total from inventories left join inventory_has_warehouses on inventory_has_warehouses.inventory_id = inventories.id left join warehouses on warehouses.id = inventory_has_warehouses.warehouse_id where inventories.subscriber_id = 2 and inventories.status = 1 and (LOWER(inventories.itemcode) LIKE %1% or LOWER(inventories.purchasedescription) LIKE %1% or exists (select * from contacts where inventories.supplier = contacts.id and LOWER(contacts.name) LIKE %1%) or (sum(inventory_has_warehouses.starting_balance) like %1%)) group by inventories.id) count_row_table)

mysql query

select inventories., SUM(inventory_has_warehouses.starting_balance) as total from inventories left join inventory_has_warehouses on inventory_has_warehouses.inventory_id = inventories.id left join warehouses on warehouses.id = inventory_has_warehouses.warehouse_id where inventories.subscriber_id = 2 and inventories.status = 1 and (LOWER(inventories.itemcode) LIKE %1% or LOWER(inventories.purchasedescription) LIKE %1% or exists (select * from contacts where inventories.supplier = contacts.id and LOWER(contacts.name) LIKE %1%) or (sum(inventory_has_warehouses.starting_balance) like %1%)) group by inventories.id

Answer 1

$id = Auth::user()->id;
        $row = DB::table('inventories')->select('inventories.*','contacts.name',DB::raw('SUM(inventory_has_warehouses.starting_balance) as total'))
            ->leftJoin('contacts', 'inventories.supplier', '=', 'contacts.id')
            ->leftJoin('inventory_has_warehouses', 'inventories.id', '=', 'inventory_has_warehouses.inventory_id')
            ->where('inventories.subscriber_id',$id)
            ->groupBy('inventory_has_warehouses.inventory_id');

if ($keyword = $request->get('search')['value']) {
            $row->having(DB::raw('SUM(inventory_has_warehouses.starting_balance)'), 'like', '%'.$keyword.'%');
            $row->orHaving('inventories.itemcode', 'like', '%'.$keyword.'%');
            $row->orHaving('inventories.purchasedescription', 'like', '%'.$keyword.'%');
            $row->orHaving('contacts.name', 'like', '%'.$keyword.'%');
          }

$datatable = DataTables::of($row)

->filterColumn('total', function($query, $keyword) {
        })

return $datatable->make(true);