Level 1
I decided to do this:
$userPosition = StatsModel::where(...)->...->limit(50)->get('_id');
$count = count($userPosition)
Jul 3, 2023
22
Level 1
Get the user's position in the database
I need to get the position of the user. To do this, I count the number of records where the value is greater than mine. In case the values are equal, then I count the values where the time is less than my time. If the values are equal and the time is equal, then I look at the date. I am making a request like this:
$userPosition = StatsModel::where('user_id', '!=', Auth::id())
->where(function ($query) use ($currentUserValue, $currentUserTime, $currentUserDate) {
$query->where('stats.value', '>', $currentUserValue)
->orWhere(function ($query) use ($currentUserValue, $currentUserTime, $currentUserDate) {
$query->where('stats.value', '=', $currentUserValue)
->where('stats,.time', '<', $currentUserTime);
})
->orWhere(function ($query) use($currentUserValue, $currentUserTime, $currentUserDate) {
$query->where('stats.value', '=', $currentUserValue)
->where('stats.time', '=', $currentUserTime)
->where('stats.date', '<', $currentUserDate);
});
})
->count();
I'm interested in the following: how to make a limit? I don't want to count all the records in the database, it's enough for me to reach, for example, up to 50 and stop counting. I tried:
->limit(50)->count();
->take(50)->count();
These methods do not help. I am using mongodb (jenssegers/mongodb)
Level 122
@deekepMaks if you only want the count, why bother with limit.
Count it and if its >50 then its 50+
The database still has to scan the entire table.
But don't you need to order the results in some way?
Level 1
@Snapey From the given request I need only count.
Why should I count all entries and load the database if I can only count a limited number, which is enough for me.
Level 122
@deekepMaks I don't understand how you can calculate the position unless you consider all rows?
1 like
Level 1
@Snapey Do you mean that I only extract _id?
Level 122
@deekepMaks No? Forget it, do what you want.
Level 1
@Snapey I did not understand you :) I extract only _id, but with different conditions in "where". I will get the array [id1, id2,...idn] and use count(array) to find out the size of the array. This will be a replacement for ->count(), but with the limit that is set in the database query ->limit(50)
This code does exactly what I need.
Level 122
@deekepMaks what I mean is
" hey great news, I'm at position 12 on the leaderboard"
" what! Out of the 350 users in the system?"
"Oh, I don't know about that, I only looked at the first 50 users"
Level 1
@Snapey I don't need to know the total number of users. It is enough for me to understand whether a player is in the top 50 or not.
Level 122
@deekepMaks You still do not understand.. I never said anything about counting the total number of users.
How can you possibly know if you are in the top 50 OF ALL USERS unless you consider ALL USERS
Level 1
@Snapey “Out of the 350 users in the system?”
Level 122
@deekepMaks it was a hypothetical discussion between two people about a leaderboard.
Level 75
@deekepmaks why would you need the exact position in the database?
Level 1
@jlrdw I need to create a leaderboard. I am fetching users using pagination. I define the positions of all users already on the client when I build the interface.
But I also need to get the position of the person who views this top. If a person is not included, for example, in the top 50, then it makes no sense to count the position in the database more than 50, I'll just show "You're not in the top". So I count the number of entries that is "above" my user.
Level 75
@deekepMaks use a ratings field, position in database, in my opinion, doesn't make sense.
Level 1
@jlrdw Use the rating field? Then I will have to update the rating every time the user statistics are updated
Level 75
@deekepMaks use statistics then. In my opinion you aren't being clear on what you need, i.e., now you mentioned statistics.
Level 1
@jlrdw I use the statistics that are stored in the database
Level 11
@deekepmaks why don't you just order users by value, time, date (limit 50 if you want) get user ids and then find the position of your needed user id in the collection.
May be not as effective if there are a lot of users, but the code would be much more readable.
Also, as others said, you weren't fully clear on what data exactly you want to present, so hard to provide the exact advice.
1 like
Level 1
@PovilasKorop I can do as you described. The query I wrote in the topic was for use ->count()
The task of this topic is to use ->count along with the limit, but since this is not possible, I found another solution that I have already written above
Level 11
@deekepMaks oh you have a specific task to use COUNT and LIMIT, instead of actually solving a leaderboard. Weird, but ok, if you found a solution, great then.
1 like
Level 1
@PovilasKorop OK. What ways do you suggest to create a leaderboard?
Please or to participate in this conversation.