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);

Answer 2

try to group by inventory_has_warehouses.inventory_id

Edited answer:

Your really giving hardtime for someone to read your query and give you help but to correct your query here is a reformatted query with corrections:

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

the problem with query you sent is your like statement only have this:

LIKE %1%

this statement expects strings and it only says:

inventories.

this should be specific to a column you need or just use inventories.* to display all columns of that table but the error still doesn't make sense because it says and:

select count() as aggregate

maybe one of those would solve but upon reformatting your code I notice firstly the syntax error but this is very basic maybe you can start on just running a very simple query for the moment this might be the query that would work for you:

SELECT 
    inventories.id AS inventory_id,
    warehouses.id,
    SUM(inventory_has_warehouses.starting_balance) AS total
    FROM
    inventories LEFT JOIN inventory_has_warehouses
    ON inventories.id = inventory_has_warehouses.inventory_id
    LEFT JOIN
    warehouses 
    ON warehouses.id = inventory_has_warehouses.warehouse_id
    GROUP BY
    inventory_has_warehouses.inventory_id

from this start adding the conditions one by one until the error appears again (do this on mysql query window not through laravel code) not yet sure how laravel handles sql queries but the format you sent will really cause an error and also if your going to post a question here make sure to make it reader friendly or someone might slam you cause its hard to read a code that is not properly formatted. ;)

also one thing I forgot make sure that the inventories.id is the primary key of that table or this will still cause you an error refer to this link for more details https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html