Help with processing a win-loss record

[abel408]

Hello all,

I currently have a a fabrik application that keeps track of games and team records. Right now, a teams win loss record is updated after a game score is submitted. So after the game form is submitted, it calculates the winner based on score and adds a win to the win column of the teams record:

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query
      -> update('teams')
      -> set('wins = wins + 1')
      -> where('id = '. $db->quote($winning_team_name));
$db->setQuery($query);
$found = (int) $db->execute();
$query
      -> clear()
      -> update('teams')
      -> set('losses = losses + 1')
      -> where('id = '. $db->quote($losing_team_name));
$db->setQuery($query);
$found = (int) $db->execute();

The flaw with this is that it will perform every time the form is submitted. This will cause the form to perform an update on the win and loss column every time the forum is updated.

I think what I want to do is calculate the win and loss fields in my list view instead. I should create a winner_id and a loser_id element in my game list and the win loss field of each team should calculate how many times they appear in winners_id or losers_id.

For example:
wins = how many times their team_id is listed in the winners_id column of the games list.

I'm just not sure how to do this. Could someone help explain the best way to go about this? Am I on the right track of using a calc element in my list to calculate the win and loss record based on the number of times they appear in winner_id and loser_id column? Do you think their is a better / simpler way?

Thanks for any help!

 

[cheesegrits]

The way I do this kind of thing is rather than doing that kind of running, incremental update on the "team", I just re-calc the whole thing for those teams in the form submission.

So say your "matches" table has team_one, team_two, one_score and two_score, as per this Stack Overflow example ...

https://stackoverflow.com/questions/22515816/query-wins-and-losses-with-score

... and modifying that query to add your two team ID's (and remove the Score which we don't need) ...

// get both teams id's
$team1 = '{yourtable___team_one_raw}';
$team2 = '{yourtable___team_two_raw}';

// if we don't have both teams, Do Not Pass Go, Do Not Collect $200
if (!empty($team1) && !empty($team2)) {
    $myDb = JFactory::getDbo();
   // query to figure out total wins and losses for both teams
    $myQuery = "
    SELECT team, SUM(Win) As Won, SUM(Loss) as Lost
    FROM
    (
        SELECT team_one as team,
            CASE WHEN one_score > two_score THEN 1 ELSE 0 END as Win,
            CASE WHEN one_score < two_score THEN 1 ELSE 0 END as Loss
            WHERE team_one = '$team1' OR team_one = '$team2'
            FROM matches
        UNION ALL
        SELECT team_two as team,
            CASE WHEN two_score > one_score THEN 1 ELSE 0 END as Win,
            CASE WHEN two_score < one_score THEN 1 ELSE 0 END as Loss
            WHERE team_one = '$team1' OR team_one = '$team2'
        FROM matches
    ) t
    GROUP BY team
    ORDER By Won, Lost DESC
    ";

    $myDb->setQuery($myQuery);
    $results = $myDb->loadObjectList();

    // grab a query builder object
    $myQuery = $myDb->getQuery(true);

    // loops round both teams ...
    foreach ($results AS $result) {
        // query to set wins/losses for team
        $myQuery->clear()
            ->update('teams')
            ->set('wins = ' . $myDb->quote($result->Won))
            ->set('losses = ' . $myDb->quote($result->Lost))
            ->where('id = ' . $myDb->quote($result->team));
        $myDb->setQuery($myQuery);
        $myDb->execute();
    }
}

Obviously change the table and field names as appropriate.

Do that onAfterProcess in a PHP form plugin. That'll then re-calculate the wins and losses for both teams, from first principles.

-- hugh

 

[abel408]

This is great and it works like a charm!

Couple of corrections with your example in case anyone else stumble upon this:

// get both teams id's
$team1 = '{yourtable___team_one_raw}';
$team2 = '{yourtable___team_two_raw}';

// if we don't have both teams, Do Not Pass Go, Do Not Collect $200
if (!empty($team1) && !empty($team2)) {
    $myDb = JFactory::getDbo();
   // query to figure out total wins and losses for both teams
    $myQuery = "
    SELECT team, SUM(Win) As Won, SUM(Loss) as Lost
    FROM
    (
        SELECT team_one as team,
            CASE WHEN one_score > two_score THEN 1 ELSE 0 END as Win,
            CASE WHEN one_score < two_score THEN 1 ELSE 0 END as Loss
        FROM matches
        WHERE team_one = '$team1' OR team_one = '$team2'
        UNION ALL
        SELECT team_two as team,
            CASE WHEN two_score > one_score THEN 1 ELSE 0 END as Win,
            CASE WHEN two_score < one_score THEN 1 ELSE 0 END as Loss
        FROM matches
        WHERE team_two = '$team1' OR team_two = '$team2'
    ) t
    GROUP BY team
    ORDER By Won, Lost DESC
    ";

    $myDb->setQuery($myQuery);
    $results = $myDb->loadObjectList();

    // grab a query builder object
    $myQuery = $myDb->getQuery(true);

    // loops round both teams ...
    foreach ($results AS $result) {
        // query to set wins/losses for team
        $myQuery->clear()
            ->update('teams')
            ->set('wins = ' . $myDb->quote($result->Won))
            ->set('losses = ' . $myDb->quote($result->Lost))
            ->where('id = ' . $myDb->quote($result->team));
        $myDb->setQuery($myQuery);
        $myDb->execute();
    }
}

Thanks very much for the help.

 

[cheesegrits]

You're welcome. Thanks for the corrected code.

-- hugh