Modify Sql query to Eloquent query

If it works why not use as is with db facade, an example: https://laracasts.com/discuss/channels/laravel/sql-native-to-query-builder

Active record has to convert back to your or a similar sql query at run time.

@jlrdw , it works but sometime am confused to join with another eloquent, example I want to get data in Current week in mysql is not easy than eloquent as use on carbon\carborn

so that why i asking if there is the way i can use the same way as eloquent

Just example, not your data:

$quy = Powner::query()->leftJoin('dc_pets', 'dc_powners.ownerid', '=', 'dc_pets.ownerid')
                ->select('dc_powners.ownerid', 'dc_powners.oname')
                ->selectRaw('count(dc_pets.petid) as countOfPets')
                ->groupby('dc_powners.ownerid')
                ->orderby('dc_powners.oname')
                ->get();

Results basically give:

ownerid, oname, countOfPets

Study the various chapters on query builder, eloquent, etc.

Like:

5|Bob|3
4|Greg|9
2|Rob|1

Just test data I use.

@jlrdw , i tested in my side example this i want to calculate the closedbalance but i get this error

         $open=Product::select('open_stock')->where('id',6)->first();
       // dd($open);

    $data= Stock::select(DB::raw('sum(qty) as sqty'),'pid','Indate')
    ->whereBetween('Indate', [$start_week, $end_week])
  ->where('pid',6)
   ->get();

   $data2= Loading::select(DB::raw('sum(qty) as lqty'),'pid','Outdate')
    ->whereBetween('Outdate', [$start_week, $end_week])
    ->where('pid',6)
     ->get();

      $closedbalance= $data->sqrty + $open->open_stock - $data2->lqrty;

   dd($closedbalance);

error

                  Property [sqty] does not exist on this collection instance.

can you give the way how i can do calculation like that?

@emfinanga you need to call first() on $data. Or instead of get() replace it with first().

@emfinanga Of course that was just an example.

@bugsysha , for the first id is works fine

but how for each id because i want to calculate for each id

because i want to display for each items for current week that why i used get()

      $weekdata_in=DB::table('products')
    ->select(DB::raw('ifnull(sum(stocks.qty),0) as sqty_curr'),'products.name as name')
    ->join('stocks', 'stocks.pid','=','products.id')
     ->whereBetween('stocks.Indate', [Carbon::now()->startOfWeek(), Carbon::now()->endOfWeek()])
     ->get();

    $weekdata_out=DB::table('products')
    ->select(DB::raw('ifnull(sum(loadings.qty),0) as lqty_curr'),'products.name as name')
    ->join('loadings', 'loadings.pid','=','products.id')
    ->whereBetween('loadings.Outdate', [Carbon::now()->startOfWeek(), Carbon::now()->endOfWeek()])
    ->get();

$startOfWeek = Carbon::now()->startOfWeek();
$endOfWeek = Carbon::now()->endOfWeek();

$week_data = DB::table('products')->select(
        'products.id',
        'products.name',
        DB::raw('ifnull(sum(stocks.qty),0) as sqty_curr'),
        DB::raw('ifnull(sum(loadings.qty),0) as lqty_curr'),
        'products.open_stock',
        DB::raw('ifnull(sum(ifnull(sum(stocks.qty),0) + open_stock - ifnull(sum(loadings.qty),0)),0)'))
    ->join('stocks', 'stocks.pid', '=', 'products.id')
    ->join('loadings', 'loadings.pid', '=', 'products.id')
    ->whereBetween('stocks.Indate', [$startOfWeek, $endOfWeek])
    ->whereBetween('loadings.Outdate', [$startOfWeek, $endOfWeek])
    ->groupBy('products.id')
    ->get();

This should be in the right ballpark, and fetch all the info you're looking for in a single query.

@willvincent , i get this error

              SQLSTATE[HY000]: General error: 1111 Invalid use of group function (SQL: select `products`.`id`, 
           `products`.`name`, ifnull(sum(stocks.qty),0) as sqty_curr, ifnull(sum(loadings.qty),0) as lqty_curr, 
          `products`.`open_stock`, ifnull(sum(ifnull(sum(stocks.qty),0) + open_stock - 
          ifnull(sum(loadings.qty),0)),0) as CB from `products` inner join `stocks` on `stocks`.`pid` = 
      `products`.`id` inner join `loadings` on `loadings`.`pid` = `products`.`id` where `stocks`.`Indate` 
        between 2020-05-04 00:00:00 and 2020-05-10 23:59:59 and `loadings`.`Outdate` between 2020-05-04 
        00:00:00 and 2020-05-10 23:59:59 group by `products`.`id`)

@willvincent , have you know what was the problem here?

Try it without the groupBy

Does not works get the same error

it's in the ballpark.. you just need to work through it.. I don't have your DB, so am mostly shooting in the dark.