为什么这个函数返回空数据？

I'm trying to Merge PurchaseLine Table with SellLine to get Quantity Sold | Quantity Purchased | Stock Balance

This query works without joining purchaselines Table. With purchaselines Table it returns OK but Data is empty

I think i have issue with the Where, WhereIn, Orwhere clause?

See the Attached Image below. Why will data be empty?

  if ($request->ajax()) {
            $variation_id = $request->get('variation_id', null);
            $query = TransactionSellLine::join(
                'transactions as t',
                'transaction_sell_lines.transaction_id',
                '=',
                't.id'
            )
                ->join(
                    'variations as v',
                    'transaction_sell_lines.variation_id',
                    '=',
                    'v.id'
                )
                ->join('product_variations as pv', 'v.product_variation_id', '=', 'pv.id')
                ->join('purchase_lines as pl', 'pl.transaction_id', '=', 't.id')
                ->join('products as p', 'pv.product_id', '=', 'p.id')
                ->join('users as z', 't.created_by', '=', 'z.id')
                ->leftjoin('units as u', 'p.unit_id', '=', 'u.id')
                ->where('t.business_id', $business_id)
                ->whereIn('t.type', ['sell', 'purchase', 'opening_stock'])
                ->orwhereIn('t.status', ['final', 'receieved'])
                ->select(
                    'p.name as product_name',
                    'z.username as username',
                    'p.enable_stock',
                    'p.type as product_type',
                    'pv.name as product_variation',
                    'v.name as variation_name',
                    't.id as transaction_id',
                    't.transaction_date as transaction_date',
                    'transaction_sell_lines.unit_price_before_discount as unit_price',
                    DB::raw('DATE_FORMAT(t.transaction_date, "%Y-%m-%d") as formated_date'),
                    DB::raw("(SELECT SUM(vld.qty_available) FROM variation_location_details as vld WHERE vld.variation_id=v.id $vld_str) as current_stock"),
                    DB::raw('SUM(transaction_sell_lines.quantity - transaction_sell_lines.quantity_returned) as total_qty_sold'),
                    'u.short_name as unit',
                    DB::raw('SUM(pl.quantity - pl.quantity_returned - pl.quantity_adjusted) as qty_purchased'),
                    DB::raw('SUM((transaction_sell_lines.quantity - transaction_sell_lines.quantity_returned) * transaction_sell_lines.unit_price_inc_tax) as subtotal')
                )
                ->groupBy('v.id')
                ->groupBy('pl.id')
                ->groupBy('formated_date');
 return Datatables::of($query)
 ->rawColumns(['qty_purchased','current_stock', 'subtotal', 'total_qty_sold'])
 ->make(true);

I removed the purchaselines Join as it not necessary this line

  ->join('purchase_lines as pl', 'pl.transaction_id', '=', 't.id')

I am supposed to query the purchaselines table using product id instead of transaction id

Below is the modification that works

DB::raw('(SELECT SUM(pl.quantity - pl.quantity_returned - pl.quantity_adjusted) FROM purchase_lines as pl WHERE pl.product_id=pv.id) as qty_purchased')