I'm getting hung up on the logic needed in php to assign my users to a "league" based on their performance. Basically, once a month I will have a cron job run which will fetch a list of my users and their data averaged like so
SELECT user_id, AVG(activity_value) AS `average`
FROM usermeta
WHERE activity_date >= DATE_ADD(CURDATE(), INTERVAL -7 DAY)
GROUP BY user_id
ORDER BY average DESC
This returns a list of my users and their average which will determine their placement in the leagues (best user at the top).
I have a leagues table which simply stores the league id and its name like so
league_id league_name
1 Gold
2 Silver
3 Bronze
Then I have a relation table that ties the user and league tables together which (should) look like so once I get the logic pinned down
user_id league_id
2 1
3 1
1 2
6 2
5 3
4 3
etc...
Where i'm getting hung up is the logic needed to insert the user to the correct league. There is the possibility that there can be more leagues too. So for instance, if there are 36 users and 4 leagues, each league should have 9 users in it.
My cron job so far is running this on execution (just to give you an idea of my progress)
protected function execute() {
$leagues = $this->app['leagues'];
$userAvgs = $leagues->getAllUserAvgs(); // holds results from query shown above
foreach($userAvgs as $user) {
$league = new League(array(
'user_id' => $user['user_id'],
'league_id' => THE_CORRECT_LEAGUE_HERE
));
$leagues->save($league);
}
}
On each loop of the foreach it should store to the relation table the user and their proper league, but i'm just lost on how to run through it and determine the right league I need to insert for the users league_id. I guess I need to have the correct user number per league which could be along the lines of
$usersPerLeague = count($userAvgs)/count($leagues->getAllLeagues());
// getAllLeagues method returns a list of my leagues obviously
If anyone can guide me in the right direction on this process, i'd much appreciate it.
This is an alternative solution
$all_leagues= $leagues->getAllLeagues();
$all_users = $users->getAllUserAvgs();
$num_leagues = count($all_leagues);
$num_users = count($all_users);
$usersPerLeague = $num_users / $num_leagues;
$a=0;
$b=0;
foreach($all_users as $user) {
$a++;
if($a % $usersPerLeague == 0){
$b++;
}
$league = new League(array(
'user_id' => $user['user_id'],
'league_id' => $all_leagues[$b]
));
$leagues->save($league);
}
in the sql order you can add the user_id order
ORDER BY average,user_id DESC