How to get a row position using eloquent

Question

I have a leaderboard which shows the top people who have had the most sales.

It's simple. It just gets the top 25 and displays them

$user = \App\User::orderBy('sales', 'desc')
        ->take(25)
        ->get();

So that could end up with

+-------+-------------------+
|  pos  |user_id|   sales   |
----------------------------|
| 1     |    1  | 1,293     |
| 2     |   99  | 1,093     |
| 3     |   45  | 985       |
| 4     |  948  | 900       |
| 5     |   39  | 889       |
| 6     |   29  | 887       |
+---------------------------+

Now how can I get the position of the user who is viewing the leaderboard if they are not in the top 25?

If the user id 219 is at the position 935 I want to display something like this at the end

+-------+-------------------+
|  pos  |user_id|   sales   |
|---------------------------|
| 935   |  219  |     87    |
+---------------------------+

How is this possible to do efficiently without counting up to the user? (As there are thousands of users)

$user = \App\User::find($userId, ['pos']); $pos = $user->pos; — Davit Zeynalyan, Jun 02 '18 at 09:03
Yep `pos` isn't a field. I'm just counting the top 25 in a loop. — GalahadXVI, Jun 02 '18 at 09:06
you can see [https://stackoverflow.com/questions/1895110/row-number-in-mysql/1895127#1895127](https://stackoverflow.com/questions/1895110/row-number-in-mysql/1895127#1895127) — Davit Zeynalyan, Jun 02 '18 at 09:13

score 4 · Accepted Answer · answered Jun 02 '18 at 09:07

4

I suppose you have current user data. So, you don't have to query database to get current user id or sales. So, the main problem is to find count of users who have more sales than current.

So, you need a query like

SELECT COUNT(*) as pos FROM your_table WHERE sales > %current_user_sales%

Translate it to laravel query and add 1 when ouptutting.

answered Jun 02 '18 at 09:07

u_mulder

54,101
5
48
64

Wow, both simple and effective. Thanks! – GalahadXVI Jun 02 '18 at 09:12
Tested in mysql - you can use `COUNT() + 1` in a query. – u_mulder Jun 02 '18 at 09:13
Thanks for the help! – GalahadXVI Jun 02 '18 at 09:16
Not my exact issue but this totally simplified it and gave me the solution. BRAVO! – Overcomer Jan 22 '23 at 20:55

How to get a row position using eloquent

1 Answers1