I need help to figure out how to solve this:
We are trying to create a fixture list for a tournament, below you will find a table generated by a webinterface we have. (Not sure if this is the ideal layout to make the fixture list, but I may do changes to what we store and use as a basis for the task below).
The tournament concept is with x number of courts in the same location. So we would like to have court utilization as close to 100% as possible. The problem is to generate the fixture list itself based on this table without conflicts( a team plays 2 games in same round) and also to have the best utilization of the courts.
Explanation of the concept: Tournament_id is the id of the main tournament. Then we have sub tournaments, and they are identified by pool_id. Then each pool may have several groups which play round robin (single/double) group_id. We have already done the setup of games so the teams playing each other are located in home and visit, and the round within the group is indicated by round. This means if a group has 2 games in round 1 this is the maximum games that can be played in this group for this round, if only one game the maximum is 1.
priority is used as this: Equal priority means the games should be handled together during creation of fixture list. In the example pool_id=23 has 3 groups, 0,1,2. 1 and 2 has priority 1, and 0 has priority 2. This means the games for group 0 cannot be played before the games in priority 1 for groups 1 and 2 has been played. (it is used to keep control inside each pool.)
The number of courts in the tournament is known at this stage and for this example it's 4.
I would like to be able to generate the fixture list in 2 different ways:
1: We use the courts column and assign to correct court.
2: We do not use the courts column, and assign games to next available court.
I would like to have an array which I can loop through to make the fixture list, game by game, round by round with indication of the court it is assigned.
mysql> select * from tournament_games where tournament_id=7 order by priority,round,fetch_order; +---------------+---------+----------+-------+------+-------+--------+----------+-------------+-----------------------+ | tournament_id | pool_id | group_id | round | home | visit | courts | priority | fetch_order | gametype | +---------------+---------+----------+-------+------+-------+--------+----------+-------------+-----------------------+
| 7 | 20 | 1 | 1 | 156 | 49 | 4 | 1 | 1 | |
| 7 | 20 | 2 | 1 | 80 | 71 | 3 | 1 | 2 | |
| 7 | 18 | 1 | 1 | 69 | 64 | 3-4 | 1 | 4 | |
| 7 | 18 | 1 | 1 | 68 | 65 | 3-4 | 1 | 4 | |
| 7 | 23 | 1 | 1 | 155 | 50 | 1 | 1 | 5 | |
| 7 | 23 | 2 | 1 | 153 | 100 | 2 | 1 | 6 | |
| 7 | 20 | 1 | 2 | 49 | 79 | 4 | 1 | 1 | |
| 7 | 20 | 2 | 2 | 71 | 74 | 3 | 1 | 2 | |
| 7 | 18 | 1 | 2 | 64 | 68 | 3-4 | 1 | 4 | |
| 7 | 18 | 1 | 2 | 69 | 65 | 3-4 | 1 | 4 | |
| 7 | 23 | 1 | 2 | 50 | 99 | 1 | 1 | 5 | |
| 7 | 23 | 2 | 2 | 100 | 151 | 2 | 1 | 6 | |
| 7 | 20 | 1 | 3 | 156 | 79 | 4 | 1 | 1 | |
| 7 | 20 | 2 | 3 | 74 | 80 | 3 | 1 | 2 | |
| 7 | 18 | 1 | 3 | 65 | 64 | 3-4 | 1 | 4 | |
| 7 | 18 | 1 | 3 | 69 | 68 | 3-4 | 1 | 4 | |
| 7 | 23 | 1 | 3 | 155 | 99 | 1 | 1 | 5 | |
| 7 | 23 | 2 | 3 | 153 | 151 | 2 | 1 | 6 | |
| 7 | 20 | 1 | 4 | 49 | 156 | 4 | 1 | 1 | |
| 7 | 18 | 1 | 4 | 65 | 68 | 3-4 | 1 | 4 | |
| 7 | 18 | 1 | 4 | 64 | 69 | 3-4 | 1 | 4 | |
| 7 | 23 | 1 | 4 | 50 | 155 | 1 | 1 | 5 | |
| 7 | 23 | 2 | 4 | 100 | 153 | 2 | 1 | 6 | |
| 7 | 20 | 1 | 5 | 79 | 49 | 4 | 1 | 1 | |
| 7 | 18 | 1 | 5 | 65 | 69 | 3-4 | 1 | 4 | |
| 7 | 18 | 1 | 5 | 68 | 64 | 3-4 | 1 | 4 | |
| 7 | 23 | 1 | 5 | 50 | 99 | 1 | 1 | 5 | |
| 7 | 23 | 2 | 5 | 100 | 151 | 2 | 1 | 6 | |
| 7 | 20 | 1 | 6 | 79 | 156 | 4 | 1 | 1 | |
| 7 | 18 | 1 | 6 | 69 | 68 | 3-4 | 1 | 4 | |
| 7 | 18 | 1 | 6 | 65 | 64 | 3-4 | 1 | 4 | |
| 7 | 23 | 1 | 6 | 99 | 155 | 1 | 1 | 5 | |
| 7 | 23 | 2 | 6 | 151 | 153 | 2 | 1 | 6 | |
| 7 | 20 | 0 | 1 | 0 | 0 | 3-4 | 2 | 3 | partidos de posición |
| 7 | 20 | 0 | 1 | 0 | 0 | 3-4 | 2 | 3 | partidos de posición |
| 7 | 20 | 0 | 1 | 0 | 0 | 3-4 | 2 | 3 | partidos de posición |
| 7 | 20 | 0 | 1 | 0 | 0 | 3-4 | 2 | 3 | partidos de posición |
| 7 | 23 | 0 | 1 | 0 | 0 | 1-2 | 2 | 7 | semifinales |
| 7 | 23 | 0 | 1 | 0 | 0 | 1-2 | 2 | 7 | semifinales |
| 7 | 23 | 0 | 2 | 0 | 0 | 1-2 | 2 | 7 | final y perdedor |
| 7 | 23 | 0 | 2 | 0 | 0 | 1-2 | 2 | 7 | final y perdedor |
+---------------+---------+----------+-------+------+-------+--------+----------+-------------+-----------------------+ 41 rows in set (0.00 sec)
Anyone with any suggestions on how to go about solving this?
regards, Roar
