aboutsummaryrefslogtreecommitdiffstats
path: root/include
diff options
context:
space:
mode:
authorrvelices <rv-github@modusoptimus.com>2011-07-25 18:04:50 +0000
committerrvelices <rv-github@modusoptimus.com>2011-07-25 18:04:50 +0000
commit4e4dc79e5162a7d1315b12e94153443552530b9e (patch)
treefeec6972f3ff9eb108ce87fe672414b98da69a85 /include
parent86bc4d1143474dcb5b9a65c1affd62d7e1b14c0c (diff)
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
Diffstat (limited to '')
-rw-r--r--include/functions_category.inc.php2
-rw-r--r--include/functions_rate.inc.php93
-rw-r--r--include/picture_rate.inc.php13
-rw-r--r--include/ws_functions.inc.php12
4 files changed, 95 insertions, 25 deletions
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();