Laravel 9: Game: User rank generation

Hi all!

I'm working on a game platform for friends and family. This will allow the user to make predictions on the upcoming WC (football) games. What I'm trying to do is creating a table where the users are shown ranked by points. They gather those points by prediction the correct score of a game in a certain stage. What else is important, is that in that table (view) the user is able to see if he/she is climbed of declined the ladder by n-places. Also how many points there is added as of the previous game.

I am working on a gaming platform for friends and family. It allows users to make predictions about the upcoming World Cup (football) matches. What I am trying to do is create a table where users are ranked by points. They collect those points by predicting the correct score of a match at a particular stage. What is further important is that in that table (view), the user can see if they have climbed/declined the ladder by n places. Also how many points have been added from the previous game.

Expected result:

rank

Simplified DB schema: GAME id date home_country_id away_country_id stage_id home_score away_score calculated

PREDICTION id game_id user_id home_score away_score points

STAGE (Group stage, quarter final, semi final, ...) id name

The idea is also to create a graph showing the ranks between each match. Also who is the winner of each stage. At first I created separate data sets and merged them, but this was very slow in output .... I came up with a rather complex SQL query that is not watertight ... and also very slow after a few matches.

Any ideas on how to tackle this puppy neat and efficiently?

JakeCausier

3 years ago

Level 6

A simple way of doing this is to create a history entry for the user's score after a match, showing the increase or decrease in their ranking score. After each match the previous entry can be looked at, change the score of it, then create a new entry with that score.

The downside of this is that it won't be dynamic based directly on the data of the scores table, so any fixes post-match would not be reflected across the user scores.

Another method is to calculate the score independent of the SQL query and store the value somewhere, then return it to the user. This could be a virtual column in the database that is calculated based on the game scores. To prevent this calculation taking up too much memory and running every time its called, you can implement caching that is refreshed after each new game that happens.

Please or to participate in this conversation.