From 4e4dc79e5162a7d1315b12e94153443552530b9e Mon Sep 17 00:00:00 2001 From: rvelices Date: Mon, 25 Jul 2011 18:04:50 +0000 Subject: feature 2384: improve average rating calculation (still need to update language files) git-svn-id: http://piwigo.org/svn/trunk@11827 68402e56-0260-453c-a942-63ccdbb3a9ee --- include/functions_category.inc.php | 2 +- include/functions_rate.inc.php | 93 ++++++++++++++++++++++++++++++++------ include/picture_rate.inc.php | 13 ++---- include/ws_functions.inc.php | 12 +++-- 4 files changed, 95 insertions(+), 25 deletions(-) (limited to 'include') diff --git a/include/functions_category.inc.php b/include/functions_category.inc.php index 30cf7d2e7..23509a684 100644 --- a/include/functions_category.inc.php +++ b/include/functions_category.inc.php @@ -290,7 +290,7 @@ function get_category_preferred_image_orders() return trigger_event('get_category_preferred_image_orders', array( array(l10n('Default'), '', true), - array(l10n('Average rate'), 'average_rate DESC', $conf['rate']), + array(l10n('Rating score'), 'average_rate DESC', $conf['rate']), array(l10n('Most visited'), 'hit DESC', true), array(l10n('Creation date'), 'date_creation DESC', true), array(l10n('Post date'), 'date_available DESC', true), diff --git a/include/functions_rate.inc.php b/include/functions_rate.inc.php index a5c6084c8..de52c37f3 100644 --- a/include/functions_rate.inc.php +++ b/include/functions_rate.inc.php @@ -116,21 +116,88 @@ INSERT ;'; pwg_query($query); - // update of images.average_rate field + return update_rating_score($image_id); +} + + +/* update images.average_rate field + * we use a bayesian average (http://en.wikipedia.org/wiki/Bayesian_average) with +C = average number of rates per item +m = global average rate (all rates) + + * param int $element_id optional, otherwise applies to all + * @return array(average_rate, count) if element_id is specified +*/ +function update_rating_score($element_id = false) +{ $query = ' -SELECT COUNT(rate) AS count - , ROUND(AVG(rate),2) AS average +SELECT element_id, + COUNT(rate) AS rcount, + SUM(rate) AS rsum FROM '.RATE_TABLE.' - WHERE element_id = '.$image_id.' -;'; - $row = pwg_db_fetch_assoc(pwg_query($query)); - $query = ' -UPDATE '.IMAGES_TABLE.' - SET average_rate = '.$row['average'].' - WHERE id = '.$image_id.' -;'; - pwg_query($query); - return $row; + GROUP by element_id'; + + $all_rates_count = 0; + $all_rates_avg = 0; + $item_ratecount_avg = 0; + $by_item = array(); + + $result = pwg_query($query); + while ($row = pwg_db_fetch_assoc($result)) + { + $all_rates_count += $row['rcount']; + $all_rates_avg += $row['rsum']; + $by_item[$row['element_id']] = $row; + } + + $all_rates_avg /= $all_rates_count; + $item_ratecount_avg = $all_rates_count / count($by_item); + + $updates = array(); + foreach ($by_item as $id => $rate_summary ) + { + $score = ( $item_ratecount_avg * $all_rates_avg + $rate_summary['rsum'] ) / ($item_ratecount_avg + $rate_summary['rcount']); + $score = round($score,2); + if ($id==$element_id) + { + $return = array( + 'score' => $score, + 'average' => round($rate_summary['rsum'] / $rate_summary['rcount'], 2), + 'count' => $rate_summary['rcount'], + ); + } + $updates[] = array( 'id'=>$id, 'average_rate'=>$score ); + } + mass_updates( + IMAGES_TABLE, + array( + 'primary' => array('id'), + 'update' => array('average_rate') + ), + $updates + ); + + //set to null all items with no rate + if ( !isset($by_item[$element_id]) ) + { + $query=' +SELECT id FROM '.IMAGES_TABLE .' + LEFT JOIN '.RATE_TABLE.' ON id=element_id + WHERE element_id IS NULL AND average_rate IS NOT NULL'; + + $to_update = array_from_query( $query, 'id'); + + if ( !empty($to_update) ) + { + $query=' +UPDATE '.IMAGES_TABLE .' + SET average_rate=NULL + WHERE id IN (' . implode(',',$to_update) . ')'; + pwg_query($query); + } + } + + return isset($return) ? $return : array('score'=>null, 'average'=>null, 'count'=>0 ); } ?> \ No newline at end of file diff --git a/include/picture_rate.inc.php b/include/picture_rate.inc.php index 2c0d9dd3f..086287c81 100644 --- a/include/picture_rate.inc.php +++ b/include/picture_rate.inc.php @@ -28,7 +28,8 @@ if ($conf['rate']) { - if ( NULL != $picture['current']['average_rate'] ) + $rate_summary = array( 'count'=>0, 'score'=>$picture['current']['average_rate'], 'average'=>null ); + if ( NULL != $rate_summary['score'] ) { $query = ' SELECT COUNT(rate) AS count @@ -36,18 +37,14 @@ SELECT COUNT(rate) AS count FROM '.RATE_TABLE.' WHERE element_id = '.$picture['current']['id'].' ;'; - $row = pwg_db_fetch_assoc(pwg_query($query)); + list($rate_summary['count'], $rate_summary['average']) = pwg_db_fetch_row(pwg_query($query)); } - else - { // avg rate null -> no rate -> no need to query db - $row = array( 'count'=>0, 'average'=>NULL ); - } - $template->assign('rate_summary', $row); + $template->assign('rate_summary', $rate_summary); $user_rate = null; if ($conf['rate_anonymous'] or is_autorize_status(ACCESS_CLASSIC) ) { - if ($row['count']>0) + if ($rate_summary['count']>0) { $query = 'SELECT rate FROM '.RATE_TABLE.' diff --git a/include/ws_functions.inc.php b/include/ws_functions.inc.php index 68e90f81a..04e0dbbf3 100644 --- a/include/ws_functions.inc.php +++ b/include/ws_functions.inc.php @@ -781,14 +781,20 @@ SELECT id, name, permalink, uppercats, global_rank, commentable $related_tags[$i]=$tag; } //------------------------------------------------------------- related rates - $query = ' + $rating = array('score'=>$image_row['average_rate'], 'count'=>0, 'average'=>null); + if (isset($rating['score'])) + { + $query = ' SELECT COUNT(rate) AS count , ROUND(AVG(rate),2) AS average FROM '.RATE_TABLE.' WHERE element_id = '.$image_row['id'].' ;'; - $rating = pwg_db_fetch_assoc(pwg_query($query)); - $rating['count'] = (int)$rating['count']; + $row = pwg_db_fetch_assoc(pwg_query($query)); + $rating['score'] = (float)$rating['score']; + $rating['average'] = (float)$row['average']; + $rating['count'] = (int)$row['count']; + } //---------------------------------------------------------- related comments $related_comments = array(); -- cgit v1.2.3