Laravel 5.6: how to create a subquery using Query Builder

Question

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;

$responce= DB::table('player_games')->sum('amount')->where('player_id ', 1)->order_by('id','desc')->limit(2)->first(); i think some thing like this. — atta afridi, Oct 11 '18 at 06:30
This is the query: $res = DB::table('player_games')->sum('scores')->where('player_id',1)->limit(3)->first(); — user10087184, Oct 11 '18 at 07:11

score 1 · Answer 1 · answered Oct 11 '18 at 16:16

1

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');

answered Oct 11 '18 at 16:16

Jonas Staudenmeir

24,815
6
63
109

score 0 · Answer 2 · answered Oct 11 '18 at 07:13

0

$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....

answered Oct 11 '18 at 07:13

atta afridi

111
5

It returns 120...all the scores...not limit 2 – user10087184 Oct 11 '18 at 07:20
Please see this if still facing any problem then ping me. https://stackoverflow.com/questions/24823915/how-to-select-from-subquery-using-laravel-query-builder – atta afridi Oct 11 '18 at 07:38
Hi, thanks for sharing the link...now it works...I posted the solution above. – user10087184 Oct 11 '18 at 07:54

Laravel 5.6: how to create a subquery using Query Builder

2 Answers2