Star Rating Average : How ?

[sunnyjey]

I have a list A, where a user submits multiple reports (add multiple fabrik rows). There is an Fabrik element 'rating' in List A, where one of the Editor gives Star (1-5) rating to every submitted report.

I have another list B (Profile page of Users), where I want to display Average Rating (in detail view of List B) in the form of STARS of Each user (calculated from List A).

What is the best approach to achieve this?

Thank you in advance.

 

[sunnyjey]

Friendly bump. Any pointer please.

 

[sunnyjey]

LIST A Table with rating field
View attachment 17293

I tried this calc element in LIST B

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select('SUM('.$db->quoteName('rating').')')->from('mytableA_reports')->where('userid = ' .  (int) '{tableB___userid_raw}');
$db->setQuery($query);
$sum = $db->loadResult();

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select('Count('.$db->quoteName('rating').')')->from('mytableA_reports')->where('userid = ' .  (int) '{tableB___userid_raw}');
$db->setQuery($query);
$count = $db->loadResult();

return $sum/$count;

I know this code is wrong. Looking for help.

 

[cheesegrits]

That will be erroring out because you have unbalanced quotes, as you are using single quotes for both the string of the query, and for the quoteName(). Either use double quotes for one of them, or just leave out the quoteName(), as 'rating' is not a reserved word and doesn't really need quoting.

Then you can do it in a single query using AVG().

Something like ...

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select('ROUND(AVG(rating), 1)->from('mytableA_reports')->where('userid = ' .  (int) '{tableB___userid_raw}');
$db->setQuery($query);
return $db->loadResult();

You might want to add a COALESCE around the ROUND(), to provide a default if the result of that is NULL (eg. there are no ratings for that user).

-- hugh

 

[sunnyjey]

Thank you. I got it working. Now the second step.

How do I render the obtained Average points into the Five STAR Rating ?

I have created another Rating Element in LIST B.

How do I fill and dynamically update the value of Calc Element into the Rating Element ?

 

[cheesegrits]

You can't.

You'd probably have to do this as a PHP plugin running either onBeforeLoad, and modify the table data for the rating element, or onLoad and modify the row data in the model.

-- hugh