How to merge three query in one

Where did you get stuck?

@bugsysha , i don't know the logic to join

To join what you've wrote or join in query builder? If it is the second then do you know it in MySQL?

@bugsysha , i tried like this but when i run foreach at blade view i get some error

      $open_balance_prev=DB::select(DB::raw(" SELECT products.id,products.name as name,
	(
	(products.open_stock)
	+(select ifnull(sum(stocks.qty),0) from stocks where stocks.pid=products.id and MONTH(stocks.Indate) <= MONTH(CURRENT_DATE())-1)
	- (select ifnull(sum(loadings.qty),0) from loadings where loadings.pid=products.id and MONTH(loadings.Outdate) <= MONTH(CURRENT_DATE())-1)
	) 
	as open_balance

	from products where  products.id= $id"));


$item_load_list_cur_month=DB::table('products')
->select('loadings.qty as QtyOut','loadings.Outdate','products.name as name','stocks.qty as 
       QtyIn','stocks.Indate')
->join('loadings', 'loadings.pid' ,'=','products.id')
->join('stocks', 'stocks.pid' ,'=','products.id')
->whereYear('loadings.Outdate', Carbon::now()->year)
->whereMonth('loadings.Outdate', Carbon::now()->month)
->whereYear('stocks.Indate', Carbon::now()->year)
->whereMonth('stocks.Indate', Carbon::now()->month)
->where('products.id','=', $id)
->GroupBy('loadings.Outdate','stocks.Indate')
->get();
$item_date_wise=$item_load_list_cur_month->merge( $open_balance_prev);

return view('report.item_date_wise',compact('item_date_wise'));

@bugsysha is there any way you can use it in the blade view?

You should avoid using any logic in your blade files. Post the error, maybe it's an easy fix.

@bugsysha , it says

Undefine: Variable name

I do not see any reference in what you've posted to variable name. Where do you have it?

Let me tell you my full concept,

I have 3 tables products, loadings and stock so, i want to select all record which are saved in this month where id=6 or any

After That i design the blade view, to show a report like this

Details. DateIn. DateOut. QtyIn. QtyOut. Closed balance Open balance. 100

                           02/05/2020.                        20.           Null.                 120
                                                   03/05/2020.  Null.        40.                   80

@bugsysha i have this variable in my blade view,

If you post here zoom link in next 15 minutes I'll join.

https://zoom.us/wc/96547826267/start

That does not look like a valid link. Zoom won't open it.

ok wait a moment i make new one

Join Zoom Meeting https://zoom.us/j/98985948454?pwd=K043Z2xKSW5MRGpaWUFmeUE2OHhNQT09

Meeting ID: 989 8594 8454 Password: 6uXzxe

I'm not sure if you've heard what I've said. So there is no point to join multiple queries into one when the data you get from those queries is not usable in foreach loop which you are using to display the data. Use separate queries and display the data where needed. Use that total data to subtract from when looping in order to display the correct status after that change.

@bugsysha , Ok so how can i looping in the blade view that query, can you show me the example

Like I've told you, in controller in compact pass another variable name which you have for that totals query.

@bugsysha , ok in case your free you can join, today is Up,, then i can show you something

https://zoom.us/j/98985948454?pwd=K043Z2xKSW5MRGpaWUFmeUE2OHhNQT09

Sorry I can not join. I'm working.

@bugsysha,

i managed to do like this, but i faced one challenge of calculation to get closed balance of each rows

                <?php $newCB=0; ?>
              @foreach($open_balance_prev as $dt)
          <tr>
            <td>Open balance</td>
            <td></td>
            <td></td>
            <td></td>
            <td></td>
            <td>{{$dt->open_balance}}</td>
          
          </tr>
          @endforeach
          
          @foreach($item_load_list_cur_month as $data)
          <tr>
            <td>{{$data->name}}</td>
            <td></td>
            <td>{{$data->Outdate}}</td>
            <td></td>

            <td>{{$data->QtyOut ??0 }}</td>
            <!-- get the error here can not define $dataload->QtyIn -->
            <td>{{$newCB= $dt->open_balance + 0 - $data->QtyOut ??0 }}</td>

          </tr>
          @endforeach

           @foreach($item_stock_list_cur_month as $dataload)
          <tr>
            <td></td>
            <td>{{$dataload->Indate}}</td>
            <td></td>

            <td>{{$dataload->QtyIn ??0 }}</td>
            <td></td>
             
            <td>{{$newCB= $dt->open_balance + $dataload->QtyIn - $data->QtyOut ??0 }}</td>
          </tr>
          @endforeach
        </tbody>
      </table>

if you see there is $newCB this calculate the new closed balance of each row

If you have closeBalance from previous day, then pass that to the blade view and from that subtract every day in the loop.

I passed the closed balance in the blade as you can see on the blade view, but when i do calculation i get this error

           Undefined variable: dataload (View: C:\xampp\htdocs\pacl- 
           inventory\resources\views\report\item_date_wise.blade.php)

I think you should check some videos on how to pass variables to blade. Or maybe if it is problem for you to pass variables in blade do it in controller and just pass to blade results.

@bugsysha ok brother, thank you. let me follow you advice well

Thanks for "best answer" and all best. Keep on rockin'.

@bugsysha , your advice is good but sorry can you advice this one

https://laracasts.com/discuss/channels/eloquent/modify-sql-query-to-eloquent-query

see my last comment

Replied.