Get sum by laravel subjoin

Question

I'm using an online woocommerce shop. I have several suppliers for my goods. and when my order is saved I save each item's supplier. Now I'm trying to query my database via laravel. this is my query by laravel's database facade which works alright.

$items = \DB::connection('wordpress')->table('woocommerce_order_items')
            ->Join('woocommerce_order_itemmeta as tbl_line_subtotal', function ($join) {
                $join->on('tbl_line_subtotal.order_item_id', '=', 'woocommerce_order_items.order_item_id')
                    ->where('tbl_line_subtotal.meta_key', '_line_subtotal');
            })
            ->Join('woocommerce_order_itemmeta as tbl_supplier', function ($join) use ($supplier) {
                $join->on('tbl_supplier.order_item_id', '=', 'woocommerce_order_items.order_item_id')
                    ->where([['tbl_supplier.meta_key', '=', '_supplier'], ['tbl_supplier.meta_value', '=', $supplier]]);
            })
            ->select(
                'woocommerce_order_items.order_id AS order_id',
                'tbl_line_subtotal.meta_value AS _line_subtotal',
                'tbl_supplier.meta_value AS _supplier',
            );


        $orders = \DB::connection('wordpress')->table('posts')
            ->joinSub($items, 'items', function ($join) {
                $join->on('posts.ID', '=', 'items.order_id');
            })
            ->select(
                'posts.ID as order_id',
                'items._line_subtotal',
                'items._supplier',
            )
            ->where('order_id', 141511)
            ->get();

the result is this: enter image description here

It works great but, what I really need is to get aggregate sum of suppliers for each order so I did this:


$orders = \DB::connection('wordpress')->table('posts')
   ->joinSub($items, 'items', function ($join) {
        $join->on('posts.ID', '=', 'items.order_id');
            })
        ->select(
                'posts.ID as order_id',
         //     'items._line_subtotal',
                \DB::raw('SUM(items._line_subtotal) as sum_line_subtotal') ,
                'items._supplier AS _supplier',
            )
            ->where('order_id', 141511)
            ->groupBy('order_id','_supplier')
            ->get();

But now the column items._line_subtotal suddenly becomes unknown. and system returns

Column not found: 1054 Unknown column

what I am doing wrong?

I had to simplify my original question. I have achieved my goal by using raw select query but since I have complex where clauses I need to use Laravel's DB facade.

Get sum by laravel subjoin

0 Answers0