1

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.

  • Reminds me of joomla.stackexchange.com/a/22916/12352 I'll try to craft something that directly resolves your question when I find some time. In the meantime, you are welcome to resolve it yourself and post an answer. – mickmackusa Sep 1 at 20:46
  • Thank you so much @mickmackusa !! I've been trying for a month without success. Other people have tried but failed! – Frullix Sep 2 at 6:26
  • I haven't had any free computer time to work on your specific question. You can save me some time by creating a db-fiddle demo, so that I can quickly get to work on it when I sit at my Joomla computer. I'll admit I burned most of a day researching and working on that older question -- then a week later I had a Joomla associate ask for this exact feature, so I was able get some return on my toil. – mickmackusa Sep 2 at 6:29
  • 1
    db_fiddle link: sqlfiddle.com/#!9/b1d57a7/6/0 I would like to get an HTML table result (best result of 4 races). – Frullix Sep 2 at 12:09
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.

| improve this answer | |
  • Hi @mick did you discover the mystery of the skipped data? I tried to look for errors in the data, but found nothing. – Frullix Sep 9 at 6:15
  • I couldn't find what was wrong with my code, but I assume it was with that sql that I borrowed from Stack Overflow. I eventually scrapped everything and started over. It is working now. @Fru (you can just append your limit clause to my working query) – mickmackusa Sep 9 at 16:56
  • Thank you so much Mick! It works perfectly fine and the explanation is also excellent!! – Frullix Sep 10 at 12:02
  • My pleasure. Was up until 3am -- wife was not happy -- gonna have a big sleep tonight. – mickmackusa Sep 10 at 12:04
  • I understand you perfectly, I work there day and night and also at work... – Frullix Sep 10 at 12:11
0

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();
| improve this answer | |
  • i tried, but this is the result: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(ORDER BY MAX(fs_score) DESC) AS nr,nome_e_cognome AS Player Name,MAX(`f' at line 1. I've mysql version: 5.7.28 – Frullix Sep 2 at 7:50
  • And your raw query is working fine on the same database? – Sharky Sep 2 at 8:06
  • No! I get the same error. – Frullix Sep 2 at 8:08
  • I thought "sql working version" meant the query was working. What database and version are you using? The query is fine but requires database to support RANK() function. MySQL supports it since 8.0 and MariaDB since 10.2. My guess is you're using an older version. – Sharky Sep 2 at 8:23
  • version() 5.7.28-31-log – Frullix Sep 2 at 8:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.