Laravel adding number row to query

Question

I am getting users and their points, I order them by points and paginate. Now I want to add a number row to result, so first placed user would have num. row 1, second 2 etc.

Currently I have this:

$scores=DB::table('scores')->join('users', 'users.id', '=', 'scores.user_id') 
                           ->join('sports', 'sports.id', '=', 'scores.sport_id')
                           ->join('leagues', 'leagues.id', '=', 'scores.league_id')
                           ->select('users.username','points','sports.sportName','leagues.leagueName')
                           ->orderBy($request->sort, $request->direction)->paginate(4);
return json_encode($scores);

EDIT: I am using vue-tables 2 so after API return table is created automatically and I can't print row number with foreach. Expected result would be:

Num.    Username    Points
1       test2       55
2       test5       42
3       test1       25
4       test4       5

JSON result:

{"data":[
    {"num":"1","username":"test2","points":22},
    {"num":"2","username":"test1","points":13},
    {"num":"3","username":"test4","points":12}
]

}

Is it important that the query adds the row number? Or can you use a foreach() loop? — Jonas Staudenmeir
– Jonas Staudenmeir, Commented Sep 23, 2018 at 16:29
@staskrak Because when I order by points then users get mixed up and ids are not ordered. — Miko
– Miko, Commented Sep 23, 2018 at 16:30
@JonasStaudenmeir This is in API and I need to return it in json, I could return array if that would help, but I cant print with foreach — Miko
– Miko, Commented Sep 23, 2018 at 16:32
What I meant: $number = 1; foreach($scores as $score) { $score->number = $number++; } — Jonas Staudenmeir
– Jonas Staudenmeir, Commented Sep 23, 2018 at 16:35

Jonas Staudenmeir · Accepted Answer · 2018-09-23 17:04:47Z

3

Use a foreach() loop:

$num = ($scores->currentPage() - 1) * $scores->perPage() + 1;

foreach($scores as $score) {
    $score->num = $num++;
}

answered Sep 23, 2018 at 17:04

Jonas Staudenmeir

26k7 gold badges70 silver badges114 bronze badges

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

Collectives™ on Stack Overflow

Laravel adding number row to query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related