Selecting data from two tables and make a ranking in an HTML table

Question

I want take the result from scheda_atleta table for every player and make an HTML table with the best result. I've two tables:

first named: at

+----+----------------+
| id | nome_e_cognome |
+----+----------------+
|  1 | Tom Tom        |
|  2 | Jerry          |
+----+----------------+

and second: scheda_atleta

+----+--------+----------+----------------+-----------+
| id | atleta | fs_score | qualificazione | categoria |
+----+--------+----------+----------------+-----------+
|  1 |      1 |    11,25 |              1 |         1 |
|  2 |      1 |    10,55 |              2 |         1 |
|  3 |      2 |    14,32 |              1 |         1 |
|  4 |      2 |    12,33 |              3 |         1 |
+----+--------+----------+----------------+-----------+

I try this PHP code below, but is not work:

    $db = JFactory::getDBO();

$bestAthleteScores = $db->getQuery(true)
    ->select($db->qn(["scores1.atleta", "scores1.fs_score","scores1.qualificazione","scores1.categoria"]))
    ->from($db->qn("scheda_atleta", "scores1"))
    ->leftJoin($db->qn("scheda_atleta", "scores2")
        . " ON " . $db->qn("scores1.atleta") . " = " . $db->qn("scores2.atleta")
        . " AND " . $db->qn("scores1.fs_score") . " < " . $db->qn("scores2.fs_score")
    )

    ->where($db->qn("scores2.fs_score") . " IS NULL")
    ->where($db->qn("scores1.qualificazione") ."=". 1)
    ->where($db->qn("scores1.categoria"). ' IN (' . implode(',', $db->quote(array(1,99,100))) . ')' )
    ->order($db->qn("scores1.fs_score") . " DESC");

$rankVariable = $db->getQuery(true)
    ->select("@i := 0");

$rankedBestScores = $db->getQuery(true)
    ->select($db->qn("fs_score") . ",@i := @i + 1 AS " . $db->qn("rank"))
    ->from("(" . $bestAthleteScores . ") AS " . $db->qn("best_athlete_scores"))
    ->join("CROSS", "(" . $rankVariable . ") AS " . $db->qn("rank_variable"))
    ->order($db->qn("best_athlete_scores.fs_score") . " DESC");

$bestScoreRanks = $db->getQuery(true)
    ->select($db->qn("fs_score") . ", MIN(" . $db->qn("rank") . ") AS " . $db->qn("rank"))
    ->from("(" . $rankedBestScores . ") AS " . $db->qn("ranked_best_scores"))
    ->group($db->qn("fs_score"));

$query = $db->getQuery(true)
    ->select(
        $db->qn(
            ["best_score_ranks.rank", "athlete_profile.nome_e_cognome", "athlete_profile.id", "athlete_scores.fs_score"],
            [null,                    "athlete_name",                   "athlete_id",         "best_score"]
        )
    )
    ->from($db->qn("at", "athlete_profile"))
    ->innerJoin($db->qn("scheda_atleta", "athlete_scores")
        . " ON " . $db->qn("athlete_profile.id") . " = " . $db->qn("athlete_scores.atleta"))
    ->innerJoin("(" . $bestScoreRanks . ") AS " . $db->qn("best_score_ranks")
        . " ON " . $db->qn("athlete_scores.fs_score") . " = " . $db->qn("best_score_ranks.fs_score"))

    ->where($db->qn("qualificazione")." =". 1)

    ->where($db->qn("categoria") . ' IN (' . implode(',', $db->quote(array(1,99,100))) . ')' )

    ->group($db->qn("athlete_profile.id"))
    ->order($db->qn("rank"));

$query->setLimit(24);

$db->setQuery($query);
$db->execute();
$query = $db->loadAssocList();

//echo "<pre>" , var_export($db->loadAssocList(), true) , "</pre>";

 ?>

<table class="category table table-striped table-bordered table-hover table-noheader">

<tr>
            <th>nr</th>
            <th>Atleta</th>
            <th>RANK</th>
</tr>
<tbody>
    <tr>

<?php foreach ($query as $result){ ?>
    <td><?php echo($result['rank']); ?></td>
    <td><?php echo($result['athlete_name']); ?></td>
    <td><?php echo($result['best_score']); ?> </td>
    </tr><?php } ?>

</tbody></table>

I would like to get this HTML table result (best result of 4 races):

+-----+-------------+------------+
| nr. | Player Name | Best score |
+-----+-------------+------------+
|   1 | Jerry       |      14,32 |
|   2 | Tom Tom     |      11,25 |
+-----+-------------+------------+

etc. But my code doesn't work, skip some data . Where am I wrong?
Joomla! 3.9.21 Stable and MySql 5.7

---

This raw sql would work if my db version supported RANK() OVER():

SELECT 
    RANK() OVER (ORDER BY MAX(fs_score) desc) AS nr, 
    nome_e_cognome AS "Player Name",
    MAX(fs_score) AS "Best score"
FROM scheda_atleta 
INNER JOIN at on at.id=scheda_atleta.atleta 
GROUP BY at.id, nome_e_cognome

but I want to generate the same result set using Joomla's query building helper methods.

Answer 1

I modified my answer from How to SET a user-defined MySQL variable and increment it to produce ranking values with JDatabaseQuery? and added some additional subqueries to obtain the best scores for each athlete.

I strongly recommend using "gapped ranking" because it more accurately represents athlete performance -- especially in cases of ties. Unfortunately, this improvement in result quality comes with a cost of increased query complexity/sophistication.

Essentially, there are 3 tables being joined. The athlete_profile table, a derived table containing each athlete's best score, then another derived table containing the rank assigned to each best score.

Tested Joomla code: (stand back, its a whopper)

$db = JFactory::getDBO();
// cache repeated values to prevent redundant method calls
$qn__fs_score = $db->qn("fs_score");
$qn__atleta = $db->qn("atleta");
$qn__all_scores_atleta = $db->qn("all_scores.atleta");
$qn__all_scores_fs_score = $db->qn("all_scores.fs_score");
$qn__rank = $db->qn("rank");

// prepare the where conditions to be applied at multiple positions
$wheres = [
    $db->qn("qualificazione"). " = 1",
    $db->qn("categoria"). ' IN (' . implode(',', $db->q([1,99,100])) . ')'
];

$personal_bests = $db->getQuery(true)
    ->select($qn__atleta . ", MAX(" . $qn__fs_score . ") AS " . $qn__fs_score)
    ->from($db->qn("scheda_atleta"))
    ->where($wheres)
    ->group($qn__atleta);

$bestAthleteScores = $db->getQuery(true)
    ->select($qn__all_scores_atleta . ", MAX(" . $qn__all_scores_fs_score . ") AS " . $qn__fs_score)
    ->from($db->qn("scheda_atleta", "all_scores"))
    ->innerJoin("(" . $personal_bests . ") " . $db->qn("personal_bests")
        . " ON " . $qn__all_scores_atleta . " = " . $db->qn("personal_bests.atleta")
        . " AND " . $qn__all_scores_fs_score . " = " . $db->qn("personal_bests.fs_score")
    )
    ->where($wheres)
    ->group($db->qn("all_scores.atleta"))
    ->order($qn__all_scores_fs_score . " DESC");

$rankVariable = $db->getQuery(true)
    ->select("@i := 0");

$rankedScores = $db->getQuery(true)
    ->select($qn__fs_score . ", @i := @i + 1 AS " . $qn__rank)
    ->from("(" . $bestAthleteScores . ") AS " . $db->qn("best_athlete_scores"))
    ->join("CROSS", "(" . $rankVariable . ") AS " . $db->qn("rank_variable"));

$query = $db->getQuery(true)
    ->select([
        $db->qn("athlete_profile.nome_e_cognome", "athlete_name"),
        $db->qn("athlete_profile.id", "athlete_id"),
        "MIN(" . $db->qn("ranked_scores.rank") . ") AS " . $qn__rank,
        "MAX(" . $db->qn("ranked_scores.fs_score") . ") AS " . $db->qn("best_score")
    ])
    ->from($db->qn("at", "athlete_profile"))
    ->innerJoin("(" . $personal_bests . ") " . $db->qn("athlete_bests")
        . " ON " .$db->qn("athlete_profile.id") . " = " . $db->qn("athlete_bests.atleta")
    )
    ->innerJoin("(" . $rankedScores . ") AS " . $db->qn("ranked_scores")
        . " ON " . $db->qn("athlete_bests.fs_score") . " = " . $db->qn("ranked_scores.fs_score")
    )
    ->group($db->qn(["athlete_id", "athlete_name"]))
    ->order($qn__rank);

echo $query->dump();    // testing only, don't do this when live/public
$db->setQuery($query);
$db->execute();
echo "<pre>" , var_export($db->loadAssocList(), true) , "</pre>";

This is the rendered query: (DB-FIDDLE DEMO *notice the gap at rank 38)

SELECT `athlete_profile`.`nome_e_cognome` AS `athlete_name`,
       `athlete_profile`.`id` AS `athlete_id`,
       MIN(`ranked_scores`.`rank`) AS `rank`,
       MAX(`ranked_scores`.`fs_score`) AS `best_score`
FROM `at` AS `athlete_profile`
INNER JOIN (
    SELECT `atleta`,
           MAX(`fs_score`) AS `fs_score`
    FROM `scheda_atleta`
    WHERE `qualificazione` = 1
          AND `categoria` IN ('1','99','100')
    GROUP BY `atleta`
) `athlete_bests` ON `athlete_profile`.`id` = `athlete_bests`.`atleta`
INNER JOIN (
    SELECT `fs_score`, 
           @i := @i + 1 AS `rank`
    FROM (
        SELECT `all_scores`.`atleta`,
               MAX(`all_scores`.`fs_score`) AS `fs_score`
        FROM `scheda_atleta` AS `all_scores`
        INNER JOIN (
            SELECT `atleta`,
                   MAX(`fs_score`) AS `fs_score`
            FROM `scheda_atleta`
            WHERE `qualificazione` = 1
                  AND `categoria` IN ('1','99','100')
            GROUP BY `atleta`
        ) `personal_bests` ON `all_scores`.`atleta` = `personal_bests`.`atleta`
                              AND `all_scores`.`fs_score` = `personal_bests`.`fs_score`
        WHERE `qualificazione` = 1
              AND `categoria` IN ('1','99','100')
        GROUP BY `all_scores`.`atleta`
        ORDER BY `all_scores`.`fs_score` DESC
    ) AS `best_athlete_scores`
    CROSS JOIN (
        SELECT @i := 0
    ) AS `rank_variable`
) AS `ranked_scores` ON `athlete_bests`.`fs_score` = `ranked_scores`.`fs_score`
GROUP BY `athlete_id`,
         `athlete_name`
ORDER BY `rank`

Admittedly, this is so much convolution that it may beg the question "Why not pull all of the records and perform this process in php?".

Well, if you did, I probably wouldn't blame you -- but there is every likelihood that the php solution would be complex/convoluted as well.

This solution is not for the faint-of-heart, but hopefully I've named and explained things well enough to express the why and the how of this technique.

Answer 2

Your query written using query builder:

$db = JFactory::getDbo();
$query = $db->getQuery(true)->select(
    [
            'RANK() OVER (ORDER BY MAX(' . $db->quoteName('fs_score') . ') DESC) AS ' . $db->quoteName('nr'),
            $db->quoteName('nome_e_cognome', 'Player Name'),
            'MAX(' . $db->quoteName('fs_score') . ') AS ' . $db->quoteName('Best score'),
    ]
)
    ->from($db->quoteName('scheda_atleta'))
    ->join('INNER', $db->quoteName('at') . ' ON ' . $db->quoteName('at.id') . ' = ' . $db->quoteName('scheda_atleta.atleta'))
    ->group(
        [
            $db->quoteName('at.id'),
            $db->quoteName('nome_e_cognome'),
        ]
    );

$db->setQuery($query);

Use $db->loadObjectList() or $db->loadAssocList() to load results as an array of objects or an array of associative arrays, respectively.

Note, in order to run queries in Joomla don't necessarily have to use the query builder. You can pass your SQL string directly to database driver's setQuery() method:

$db = JFactory::getDbo();
$db->setQuery('SELECT RANK() OVER (ORDER BY MAX(fs_score) desc) AS nr...');
$results = $db->loadObjectList();