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
– atta afridi, Commented Oct 11, 2018 at 6:30
This is the query: $res = DB::table('player_games')->sum('scores')->where('player_id',1)->limit(3)->first(); — user10087184
– user10087184, Commented Oct 11, 2018 at 7:11

Jonas Staudenmeir · Accepted Answer · 2018-10-11 16:16:08Z

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, 2018 at 16:16

Jonas Staudenmeir

26k7 gold badges70 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

atta afridi · Accepted Answer · 2018-10-11 07:13:48Z

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, 2018 at 7:13

atta afridi

965 bronze badges

3 Comments

user10087184 Over a year ago

It returns 120...all the scores...not limit 2

atta afridi Over a year ago

Please see this if still facing any problem then ping me. stackoverflow.com/questions/24823915/…

user10087184 Over a year ago

Hi, thanks for sharing the link...now it works...I posted the solution above.

Collectives™ on Stack Overflow

Laravel 5.6: how to create a subquery using Query Builder

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related