CODEWITHSUNDEEP
laravellaravel-5
user10087184
user10087184

Reputation: 57

Laravel 5.6: how to create a subquery using Query Builder

I would like to reproduce following mySQL query using Laravel query builder:

*SELECT SUM(scores) FROM (SELECT scores FROM player_games WHERE player_id = 1 ORDER BY id DESC LIMIT 2) scores

Any suggestions?

Here the solution:

    $sub = playerGame::where('player_id',1)->where('scores','>',0)->limit(2)->orderBy('id','desc');

    $count = DB::table( DB::raw("({$sub->toSql()}) as sub") )
   ->mergeBindings($sub->getQuery()) 
   ->sum('scores');
   return $count;

Upvotes: 0

Views: 937

Answers (2)

Jonas Staudenmeir
Jonas Staudenmeir

Reputation: 25906

Use fromSub():

$sub = playerGame::select('scores')
    ->where('player_id', 1)
    ->where('scores', '>', 0)
    ->limit(2)
    ->orderBy('id','desc');
$sum = DB::query()->fromSub($sub, 'scores')->sum('scores');

Upvotes: 1

atta afridi
atta afridi

Reputation: 96

$responce= DB::table('player_games')->where('player_id',1)->sum('amount');
        dd(collect($responce)->sortByDesc('id')->take(2));

please cheack this one.....i try it's work....and add use DB;in the top of controller....

Upvotes: 0

Related Questions