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 --- admin/configuration.php | 2 +- admin/element_set_ranks.php | 2 +- admin/include/functions.php | 61 ---------------- admin/maintenance.php | 3 +- admin/rating.php | 18 ++--- admin/themes/default/template/configuration.tpl | 2 +- admin/themes/default/template/rating.tpl | 2 + include/functions_category.inc.php | 2 +- include/functions_rate.inc.php | 93 +++++++++++++++++++++---- include/picture_rate.inc.php | 13 ++-- include/ws_functions.inc.php | 12 +++- themes/default/js/rating.js | 2 +- themes/default/template/picture.tpl | 4 +- 13 files changed, 115 insertions(+), 101 deletions(-) diff --git a/admin/configuration.php b/admin/configuration.php index 82c16b8e7..f22b0f4fb 100644 --- a/admin/configuration.php +++ b/admin/configuration.php @@ -109,7 +109,7 @@ $sort_fields = array( 'file' => l10n('File name'), 'date_creation' => l10n('Creation date'), 'date_available' => l10n('Post date'), - 'average_rate' => l10n('Average rate'), + 'average_rate' => l10n('Rating score'), 'hit' => l10n('Most visited'), 'id' => 'Id', ); diff --git a/admin/element_set_ranks.php b/admin/element_set_ranks.php index 05576777e..96084f2d1 100644 --- a/admin/element_set_ranks.php +++ b/admin/element_set_ranks.php @@ -273,7 +273,7 @@ $sort_fields = array( '' => '', 'date_creation' => l10n('Creation date'), 'date_available' => l10n('Post date'), - 'average_rate' => l10n('Average rate'), + 'average_rate' => l10n('Rate score'), 'hit' => l10n('Most visited'), 'file' => l10n('File name'), 'id' => 'Id', diff --git a/admin/include/functions.php b/admin/include/functions.php index cd03a662e..2c9c9df39 100644 --- a/admin/include/functions.php +++ b/admin/include/functions.php @@ -1050,67 +1050,6 @@ UPDATE '.IMAGES_TABLE.' } } -/** - * update images.average_rate field - * param int $element_id optional, otherwise applies to all - * @return void - */ -function update_average_rate( $element_id=-1 ) -{ - $query = ' -SELECT element_id, - ROUND(AVG(rate),2) AS average_rate - FROM '.RATE_TABLE; - if ( $element_id != -1 ) - { - $query .= ' WHERE element_id=' . $element_id; - } - $query .= ' GROUP BY element_id;'; - - $result = pwg_query($query); - - $datas = array(); - - while ($row = pwg_db_fetch_assoc($result)) - { - array_push( - $datas, - array( - 'id' => $row['element_id'], - 'average_rate' => $row['average_rate'] - ) - ); - } - - mass_updates( - IMAGES_TABLE, - array( - 'primary' => array('id'), - 'update' => array('average_rate') - ), - $datas - ); - - $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'; - if ( $element_id != -1 ) - { - $query .= ' AND id=' . $element_id; - } - $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); - } -} - /** * change the parent category of the given categories. The categories are * supposed virtual. diff --git a/admin/maintenance.php b/admin/maintenance.php index 1199af948..886739efc 100644 --- a/admin/maintenance.php +++ b/admin/maintenance.php @@ -58,7 +58,8 @@ switch ($action) case 'images' : { update_path(); - update_average_rate(); + include_once(PHPWG_ROOT_PATH.'include/functions_rate.inc.php'); + update_rating_score(); break; } case 'delete_orphan_tags' : diff --git a/admin/rating.php b/admin/rating.php index 3b06c4732..339f3044a 100644 --- a/admin/rating.php +++ b/admin/rating.php @@ -85,7 +85,7 @@ AND user_id=' . $vars['u'] . ' AND anonymous_id=\'' . $vars['a'] . '\' ;'; pwg_query($query); - update_average_rate( $vars['e'] ); + update_rating_score( $vars['e'] ); } $users = array(); @@ -100,10 +100,9 @@ while ($row = pwg_db_fetch_assoc($result)) } -$query = 'SELECT COUNT(DISTINCT(i.id)) -FROM '.RATE_TABLE.' AS r, '.IMAGES_TABLE.' AS i -WHERE r.element_id=i.id'. $page['user_filter'] . -';'; +$query = 'SELECT COUNT(DISTINCT(r.element_id)) +FROM '.RATE_TABLE.' AS r +WHERE 1=1'. $page['user_filter']; list($nb_images) = pwg_db_fetch_row(pwg_query($query)); @@ -131,7 +130,8 @@ $template->assign( $available_order_by= array( array(l10n('Rate date'), 'recently_rated DESC'), - array(l10n('Average rate'), 'average_rate DESC'), + array(l10n('Rating score'), 'score DESC'), + array(l10n('Average rate'), 'avg_rates DESC'), array(l10n('Number of rates'), 'nb_rates DESC'), array(l10n('Sum of rates'), 'sum_rates DESC'), array(l10n('File name'), 'file DESC'), @@ -164,8 +164,9 @@ SELECT i.id, i.path, i.file, i.tn_ext, - i.average_rate, + i.average_rate AS score, MAX(r.date) AS recently_rated, + ROUND(AVG(r.rate),2) AS avg_rates, COUNT(r.rate) AS nb_rates, SUM(r.rate) AS sum_rates FROM '.RATE_TABLE.' AS r @@ -207,7 +208,8 @@ ORDER BY date DESC;'; array( 'U_THUMB' => $thumbnail_src, 'U_URL' => $image_url, - 'AVG_RATE' => $image['average_rate'], + 'SCORE_RATE' => $image['score'], + 'AVG_RATE' => $image['avg_rates'], 'SUM_RATE' => $image['sum_rates'], 'NB_RATES' => (int)$image['nb_rates'], 'NB_RATES_TOTAL' => (int)$nb_rates, diff --git a/admin/themes/default/template/configuration.tpl b/admin/themes/default/template/configuration.tpl index 85574ed4b..9162cad18 100644 --- a/admin/themes/default/template/configuration.tpl +++ b/admin/themes/default/template/configuration.tpl @@ -415,7 +415,7 @@ jQuery(document).ready(function () {
    • diff --git a/admin/themes/default/template/rating.tpl b/admin/themes/default/template/rating.tpl index 0c001437a..a6d107a69 100644 --- a/admin/themes/default/template/rating.tpl +++ b/admin/themes/default/template/rating.tpl @@ -37,6 +37,7 @@ {'File'|@translate} {'Number of rates'|@translate} + {'Rating score'|@translate} {'Average rate'|@translate} {'Sum of rates'|@translate} {'Rate'|@translate} @@ -48,6 +49,7 @@ {$image.FILE} {$image.NB_RATES}/{$image.NB_RATES_TOTAL} + {$image.SCORE_RATE} {$image.AVG_RATE} {$image.SUM_RATE} 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(); diff --git a/themes/default/js/rating.js b/themes/default/js/rating.js index 668aed930..d69bd5987 100644 --- a/themes/default/js/rating.js +++ b/themes/default/js/rating.js @@ -72,7 +72,7 @@ function updateRating(e) if (gRatingOptions.ratingSummaryElement) { var t = gRatingOptions.ratingSummaryText; - var args =[result.average, result.count], idx = 0, rexp = new RegExp( /%\.?\d*[sdf]/ ); + var args =[result.score, result.count, result.average], idx = 0, rexp = new RegExp( /%\.?\d*[sdf]/ ); while (idx - {'Average rate'|@translate} + {'Rating'|@translate} {if $rate_summary.count} {assign var='rate_text' value='%.2f (rated %d times)'|@translate} - {$pwg->sprintf($rate_text, $rate_summary.average, $rate_summary.count)} + {$pwg->sprintf($rate_text, $rate_summary.score, $rate_summary.count, $rate_summary.average)} {else} {'no rate'|@translate} {/if} -- cgit v1.2.3