Order By Sum

[SoilentRed]

I have a list grouped by users id. each user has several a few rows with some scores.

I'd like to order the list descending based on the sum of the user's score. How do I do it?

 

[SoilentRed]

any help here would be greatly appreciated. seems like a simple task, but I can't find the switch

 

[cheesegrits]

I don't think there's a built in way of doing this, as ordering is only available for single fields. We just add a field to the query, like "ORDER BY foo ASC". And although we have element summing, that isn't stored in a field in the form's table.

You'd have to maintain your own 'total_score' field, probably with a form PHP submission script, running onAfterProcess, which updates the sum field after any change to the table ...

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->update('yourtable AS y')
   ->leftJoin('(SELECT user_id, SUM(score) as sum_score FROM yourtable GROUP BY user_id) AS grp ON grp.user_id = y.user_id')
   ->set('y.total_score = grp.sum_score');
$db->setQuery($query);
$db->execute();

That query should update your whole table. Change 'yourtable', 'total_score', 'user_id' and 'score' to suit your table an field names.

You could add a where() to only update the user_id for this form, but it wouldn't hurt to rebuild all the totals.

Note that if you allow rows to be deleted, you will have to add a second PHP plugin, that does exactly the same thing, running onAfterDeleteRowsForm.

You can then use the new total_score field to order your list by.

-- hugh