Advanced Ladies Elite - TEST MEDIA RANK - OK+++

$db = JFactory::getDBO();
$qn__fs_score = $db->qn("fs_score");
$qn__atleta = $db->qn("atleta");
$qn__all_scores_atleta = $db->qn("all_scores.atleta");
$qn__rank = $db->qn("rank");

$wheres = [
$db->qn("qualificazione") . " = 4",
$db->qn("macro_categoria") . " = 13",
//$db->qn("categoria") . ' IN (' . implode(',', $db->q([132,142,143])) . ')'
];

$personal_bests = $db->getQuery(true)
->select($qn__atleta . ", GROUP_CONCAT(" . $qn__fs_score . " ORDER BY " . $qn__fs_score . " DESC SEPARATOR ',') AS best_scores")
->from($db->qn("scheda_atleta"))
->where($wheres)
->group($qn__atleta);

$bestAthleteScores = $db->getQuery(true)
->select($qn__all_scores_atleta . ", SUBSTRING_INDEX(" . $db->qn("athlete_bests.best_scores") . ", ',', 2) AS best_scores")
->from("(" . $personal_bests . ") " . $db->qn("athlete_bests"))
->join("INNER", "(" . $personal_bests . ") " . $db->qn("all_scores")
. " ON " . $qn__all_scores_atleta . " = " . $db->qn("athlete_bests.atleta")
)
->group($db->qn("all_scores.atleta"))
->order("best_scores DESC");

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

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

// Sottoquery per contare il numero di risultati per ciascun atleta
$atletaResultCountSubquery = $db->getQuery(true)
->select($qn__atleta . " AS athlete, COUNT(*) AS result_count")
->from($db->qn("scheda_atleta"))
->where($wheres)
->group($qn__atleta);

$query = $db->getQuery(true)
->select([
$db->qn("athlete_profile.nome_e_cognome", "athlete_name"),
$db->qn("athlete_profile.id", "athlete_id"),
"MAX(" . $db->qn("ranked_scores.rank") . ") AS " . $qn__rank,
"ROUND((SUBSTRING_INDEX(" . $db->qn("best_athlete_scores.best_scores") . ", ',', 1) + SUBSTRING_INDEX(" . $db->qn("best_athlete_scores.best_scores") . ", ',', -1)) / 2, 3) AS best_score"
])

->from($db->qn("at", "athlete_profile"))
->innerJoin("(" . $bestAthleteScores . ") " . $db->qn("best_athlete_scores")
. " ON " . $db->qn("athlete_profile.id") . " = " . $db->qn("best_athlete_scores.atleta")
)
->innerJoin("(" . $rankedScores . ") AS " . $db->qn("ranked_scores")
. " ON " . $db->qn("best_athlete_scores.best_scores") . " = " . $db->qn("ranked_scores.best_scores")
)
->innerJoin("(" . $atletaResultCountSubquery . ") AS " . $db->qn("atleta_result_count")
. " ON " . $db->qn("athlete_profile.id") . " = " . $db->qn("atleta_result_count.athlete")
)
->where("atleta_result_count.result_count > 1") // Filtra solo gli atleti con più di un risultato
->group($db->qn(["athlete_id", "athlete_name"]))

//modifica ordinamento DESC
->order("best_score DESC");

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

$query = $db->loadAssocList();
?>

foreach ($query as $result) {
?>