combine two query into one

You are assuming that you are getting one instance, but you are getting a collection.

Change ->get() to ->first()

when i changed to first(), i get another error

       BadMethodCallException
     Call to undefined method App\CashSales::merge()

So you changed from get() to first() on both?

@bugsysha , YES

           $balanceQty=CashSales::with('items')
                    ->select('item_id',DB::raw('sum(quantity) as tqty'))
                    ->whereIn('l_type',['credit_sale','cash_sale'])

                    ->groupby('item_id')
                    ->first();

        $balanceQty1=CashSales::with('items')
                    ->select('item_id',DB::raw('sum(quantity) as tqty1'))
                    ->whereIn('l_type',['open balance','purchase'])

                    ->groupby('item_id')
                    ->first();

                    $data=$balanceQty->merge($balanceQty1);
                    

         $balanceQtyOut=(int)$balanceQty1->tqty1-(int)$balanceQty->tqty;

       // dd($data);

        return view('dashboard.index',compact('totalCredit','totalCashsale','balanceQtyOut','data'));

im using the marge, and im gettting that error

Once you switch to first() you will not get collections but single instances of CashSales. Then you do not need $data=$balanceQty->merge($balanceQty1);. If you want those two instances to be in array or collection then you can do

$data = [$balanceQty, $balanceQty1];

Once you switch to first() you will not get collections but single instances of CashSales.

YES, this is not get collection

what can i do to get the collection according to my query, can you modify it? @bugsysha

If you for some reason need both, you can do this

          $balanceQty=CashSales::with('items')
                    ->select('item_id',DB::raw('sum(quantity) as tqty'))
                    ->whereIn('l_type',['credit_sale','cash_sale'])

                    ->groupby('item_id')
                    ->get();

      $balanceQty1=CashSales::with('items')
                    ->select('item_id',DB::raw('sum(quantity) as tqty1'))
                    ->whereIn('l_type',['open balance','purchase'])

                    ->groupby('item_id')
                    ->get();

                    $data=$balanceQty->merge($balanceQty1);

       $balanceQtyOut=(int)$balanceQty1->first()->tqty1-(int)$balanceQty->first()->tqty;

be aware that you are just getting the first instance. If you instead need something like the sum of all, you can do

          $balanceQty=CashSales::with('items')
                    ->select('item_id',DB::raw('sum(quantity) as tqty'))
                    ->whereIn('l_type',['credit_sale','cash_sale'])

                    ->groupby('item_id')
                    ->get();

      $balanceQty1=CashSales::with('items')
                    ->select('item_id',DB::raw('sum(quantity) as tqty1'))
                    ->whereIn('l_type',['open balance','purchase'])

                    ->groupby('item_id')
                    ->get();

                    $data=$balanceQty->merge($balanceQty1);

       $balanceQtyOut=(int)$balanceQty1->sum('tqty1')-(int)$balanceQty->sum('tqty');

I think @sinnbeck covered all cases.

@sinnbeck , it return only one products

if possible is can share my screen -emma4082@gmail.com

$balanceIn=CashSales::with('items')

                    ->select('item_id',
                        DB::raw('sum(quantity)  as tqty whereIn(l_type,["credit_sale,cash_sale"])'),
                        DB::raw('sum(quantity)  as  tqty1 whereIn( l_type , ["open balance,purchase"]'))
                    ->groupby('item_id')
                    ->get();

I GOT THE ERROR

                  Illuminate\Database\QueryException
                SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; 
               check the manual that corresponds to your MariaDB server version for the right syntax to use near 
            'whereIn(l_type,["credit_sale,cash_sale"]), sum(quantity) as tqty1 whereIn( l_t' at line 1 (SQL: select 
         `item_id`, sum(quantity) as tqty whereIn(l_type,["credit_sale,cash_sale"]), sum(quantity) as tqty1 
            whereIn( l_type , ["open balance,purchase"] from `cash_sales` group by `item_id`)

@emfinanga I think it would be fair to select my answer as best 🤣