merge two query

how can i have one query of the below code

  $yesterday = date("Y-m-d", strtotime( '-1 days' ) );
   $stockSummary=\DB::table('stocks')
                 
                    ->select('products.name','stocks.pid','stocks.qty as stockin','loadings.qty as issued')
                        ->join('products','products.id', '=', 'stocks.pid')
                        ->join('loadings','loadings.pid','=','products.id')
                        ->whereDate('stocks.created_at', Carbon::today())
                        ->groupby('products.name','stocks.pid','stockin','issued')
                         ->get();

 $stockSummary1=\DB::table('stocks')
                    ->select('products.name','stocks.pid','stocks.qty as stockin','loadings.qty as issued')
                    ->join('products','products.id', '=', 'stocks.pid')
                        ->join('loadings','loadings.pid','=','products.id')
                         ->whereDate('stocks.created_at', $yesterday )
                          ->groupby('products.name','stocks.pid','stockin','issued')
                        
                        ->groupby('products.name','pid','stockin')
                         ->get();

Nakov

6 years ago

Level 73

You can use whereBetween and provide the two dates:

 $stockSummary1=\DB::table('stocks')
    ->select('products.name','stocks.pid','stocks.qty as stockin','loadings.qty as issued')
    ->join('products','products.id', '=', 'stocks.pid')
    ->join('loadings','loadings.pid','=','products.id')
    ->whereBetween('stocks.created_at', [ today()->subDay(), today()] )
    ->groupby('products.name','stocks.pid','stockin','issued')                    
    ->groupby('products.name','pid','stockin')
    ->get();

If that's the only difference that you have between the queries.

melx

6 years ago

Level 4

here am get only the data of today and i want data for yesterday in order to calculate the open balance

Nakov

6 years ago

Level 73

@emfinanga with the query that I shared you are getting just for today? Or you haven't tried?

I use this:

    ->whereBetween('stocks.created_at', [ today()->subDay(), today()] )

which should give you both yesterday and today.. or in two lines:

->whereDate('stocks.created_at', '>=', today()->subDay())
->whereDate('stocks.created_at', '<=', today())

Please or to participate in this conversation.