I'm making a golf website for fun, nothing I'm trying to commercialize, and am working on handicap calculations. I have the calculations for 5-19 games done fine. But when you get to 20+ games, it's getting a little trickier. I currently have two views,
"fullGames", which records every users score on a specific tee color for each course. This is only for games that have 18 holes recorded (required for handicap calculations) The fields are as follows:
Code:
score - (score for the round)
cid - (course id of the course played)
ttid - (teetime id...identifier for the game played)
gid - (golfers unique identifier for the user)
teecolor - (tee color played on)
date - (date played)
"gamesPerCourse" -- This view aggregates the fullGames based on course and tee color. The fields are as follows:
Code:
gamesPlayed (total games played for each course and teecolor)
cid (course played at)
teecolor (teecolor played at)
gid (users unique identifier)
So what happens when the site checks to see if it can calculate a handicap is does this:
1. Checks "gamesPerCourse" for entries that have "gamesPlayed" >= 5.
2. If this is the case, it then pulls the appropriate games from the "fullGames" view to calculate the handicap.
If this isn't the case, then the person hasn't played at least 5 games and a handicap can't be computed.
Now when we get to 20+ games, you only use the most recent 20 games to do the calculations. And from the most recent 20 games, you use the lowest 10 scores.
I would rather not just query the 20 most recent games from the "fullGames" view, then iterate through the 20 entries and find the 10 lowest scores.
I've tried to create a query that would keep the 20 most recent games for each user (a modification of the "fullGames" view). This query would then be used as a view and would most likely update the "fullGames" view, but when I use the limit modifier it just limits the result to that number and doesn't limit the results per gid, even though it's grouped by gid.
Am I stuck iterating through 20 rows and doing the calculations of 10 lowest scores in PHP?
Thanks for any help, any questions please ask.
~kb
