CODEWITHSUNDEEP
phpmysqllaraveleloquent
GalahadXVI
GalahadXVI

Reputation: 839

How to get a row position using eloquent

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)

Upvotes: 3

Views: 1996

Answers (1)

u_mulder
u_mulder

Reputation: 54841

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.

Upvotes: 4

Related Questions