How to get rank in laravel

Question

In my Laravel 5.3 app, votes table has a net votes column. I'd like to find the video's rank according to net votes. I'd like to display the rank like below list. I know sql @raw method. but, I want to use Laravel method. because, there are some other tables jointed with this user table and some other shorting also need to be done.

video table:

id     | net_votes| video_id | 
------ | -------: |:-------: |
1      |    5     |   1      |   
2      |    11    |   2      |   
3      |    3     |   1      |    
4      |    6     |   3      |    
5      |    5     |   2      |     

I want to get results like this

id     | net_votes| rank
------ | -------: |:----:
2      |    11    |   1
4      |    6     |   2
1      |    5     |   3
5      |    5     |   4
3      |    3     |   5

I am using this code now. its working. but I want to use Laravel Eloquent Method.

$score_board_list = DB::select("SELECT *, total, @r:=@r+1 as rank,
           @l:=total FROM ( select username, first_name, video_title, 
           net_votes, sum(net_votes) as total from videos
           LEFT JOIN users ON videos.user_id = users.id
           LEFT JOIN profile ON users.id = profile.user_id
           group by videos.id order by total desc, videos.created_at desc ) totals, (SELECT @r:=0, @l:=NULL) rank");

Answer 1

To do that

Stored your subquery into a variable

$subquery = "( 
     SELECT    username, 
               first_name, 
               video_title, 
               net_votes, 
               Sum(net_votes) AS total 
     FROM      videos 
     LEFT JOIN users 
     ON        videos.user_id = users.id 
     LEFT JOIN profile 
     ON        users.id = profile.user_id 
     GROUP BY  videos.id 
     ORDER BY  total DESC, 
               videos.created_at DESC ) totals";

then equivalent for

Select * from (subquery)

into Eloquent is

DB::table(DB::raw('subquery'))

then to select a custom column

// for example  
->select(DB::raw('@r:=@r+1 as rank'))

So your query builder would be like this

$subquery = "( 
     SELECT    username, 
               first_name, 
               video_title, 
               net_votes, 
               Sum(net_votes) AS total 
     FROM      videos 
     LEFT JOIN users 
     ON        videos.user_id = users.id 
     LEFT JOIN profile 
     ON        users.id = profile.user_id 
     GROUP BY  videos.id 
     ORDER BY  total DESC, 
               videos.created_at DESC ) totals";


$score_board_list = DB::table(DB::raw($subquery))
->select(
    '*', 
    'total', 
    DB::raw('@r:=@r+1 as rank'), 
    DB::raw('@l:=total'))
->get();