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.

Improve this question
4
  • 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. Commented Sep 1, 2020 at 20:46
  • Thank you so much @mickmackusa !! I've been trying for a month without success. Other people have tried but failed! Commented Sep 2, 2020 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. Commented Sep 2, 2020 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). Commented Sep 2, 2020 at 12:09

2 Answers 2

Reset to default
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
6
  • Hi @mick did you discover the mystery of the skipped data? I tried to look for errors in the data, but found nothing. Commented Sep 9, 2020 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) Commented Sep 9, 2020 at 16:56
  • Thank you so much Mick! It works perfectly fine and the explanation is also excellent!! Commented Sep 10, 2020 at 12:02
  • My pleasure. Was up until 3am -- wife was not happy -- gonna have a big sleep tonight. Commented Sep 10, 2020 at 12:04
  • I understand you perfectly, I work there day and night and also at work... Commented Sep 10, 2020 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
8
  • 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 Commented Sep 2, 2020 at 7:50
  • And your raw query is working fine on the same database? Commented Sep 2, 2020 at 8:06
  • No! I get the same error. Commented Sep 2, 2020 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. Commented Sep 2, 2020 at 8:23
  • version() 5.7.28-31-log Commented Sep 2, 2020 at 8:31

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.