List-Order and record the order in different groups of the same list. (view image)

[mvilela]

I need to sort the different groups in a list where records are added at different times, so the order must be updated whenever the list is displayed on the page.
I can easily establish order.
I can't get the Rank field to write that order and update it every time the list is updated.
I can't find a plugin or example on the forum.
I would appreciate help.

 

[juuser]

Hi, you will probably need to update the ranking with list php-plugin onLoad event. You would have to loop throught your list by groups and update the ranking according to total points.

EDIT: Now I remember I have done something similar in the past with PHP form plugin onAfterProcess. Something like that:

$mydb =& JFactory::getDBO();
$mydb->setQuery("SET @i := '0'");
$mydb->execute();

$mydb->setQuery("UPDATE your_table SET ranking = (@i := @i + 1)  ORDER BY your_total_score_element DESC");
$mydb->execute();

As you have several groups, you would need to add the update statements inside foreach loop and update every group separately.

 

[mvilela]

Excellent help.
It worked perfectly
Thanks.

 

[Nagtegaal]

Hi,
Good you find a solution as given bu juuser . I noticed one minor thing. The first two athletes have the same amount of points, so it might be that they should both ranked first. MySQL has a RANK (windows) function you might wanna use to establish this and give you for the first 4 athletes the rank
1
1
3
4
...

You cannot use the RANK() function directly in the UPDATE, but with a subquery you can work around this. The UPDATE statement would look something like this:
("UPDATE `your_table` INNER JOIN (
SELECT id,
RANK() OVER (PARTITION BY `your_group_by_element` ORDER BY `your_total_score_element`) AS ranking
FROM `your_table`) Q1
ON `your_table`.`id` = Q1.`id`
SET `your_table`.`rank`= Q1.`ranking` ");

In this case you do not need a variable (@i).
I'm just saying, it might be useful.

 

[mvilela]

Thank you for another solution. In this case the Rank is ordered by the fields Total_P1, serie_4, serie_3, serie_2, series_1 Desc