Changeset 11827


Ignore:
Timestamp:
Jul 25, 2011, 8:04:50 PM (9 years ago)
Author:
rvelices
Message:

feature 2384: improve average rating calculation (still need to update language files)

Location:
trunk
Files:
13 edited

Legend:

Unmodified
Added
Removed
  • trunk/admin/configuration.php

    r11589 r11827  
    110110  'date_creation' => l10n('Creation date'),
    111111  'date_available' => l10n('Post date'),
    112   'average_rate' => l10n('Average rate'),
     112  'average_rate' => l10n('Rating score'),
    113113  'hit' => l10n('Most visited'),
    114114  'id' => 'Id',
  • trunk/admin/element_set_ranks.php

    r10513 r11827  
    274274  'date_creation' => l10n('Creation date'),
    275275  'date_available' => l10n('Post date'),
    276   'average_rate' => l10n('Average rate'),
     276  'average_rate' => l10n('Rate score'),
    277277  'hit' => l10n('Most visited'),
    278278  'file' => l10n('File name'),
  • trunk/admin/include/functions.php

    r11753 r11827  
    10521052
    10531053/**
    1054  * update images.average_rate field
    1055  * param int $element_id optional, otherwise applies to all
    1056  * @return void
    1057  */
    1058 function update_average_rate( $element_id=-1 )
    1059 {
    1060   $query = '
    1061 SELECT element_id,
    1062        ROUND(AVG(rate),2) AS average_rate
    1063   FROM '.RATE_TABLE;
    1064   if ( $element_id != -1 )
    1065   {
    1066     $query .= ' WHERE element_id=' . $element_id;
    1067   }
    1068   $query .= ' GROUP BY element_id;';
    1069 
    1070   $result = pwg_query($query);
    1071 
    1072   $datas = array();
    1073 
    1074   while ($row = pwg_db_fetch_assoc($result))
    1075   {
    1076     array_push(
    1077       $datas,
    1078       array(
    1079         'id' => $row['element_id'],
    1080         'average_rate' => $row['average_rate']
    1081         )
    1082       );
    1083   }
    1084 
    1085   mass_updates(
    1086     IMAGES_TABLE,
    1087     array(
    1088       'primary' => array('id'),
    1089       'update' => array('average_rate')
    1090       ),
    1091     $datas
    1092     );
    1093 
    1094   $query='
    1095 SELECT id FROM '.IMAGES_TABLE .'
    1096   LEFT JOIN '.RATE_TABLE.' ON id=element_id
    1097   WHERE element_id IS NULL AND average_rate IS NOT NULL';
    1098   if ( $element_id != -1 )
    1099   {
    1100     $query .= ' AND id=' . $element_id;
    1101   }
    1102   $to_update = array_from_query( $query, 'id');
    1103 
    1104   if ( !empty($to_update) )
    1105   {
    1106     $query='
    1107 UPDATE '.IMAGES_TABLE .'
    1108   SET average_rate=NULL
    1109   WHERE id IN (' . implode(',',$to_update) . ')';
    1110     pwg_query($query);
    1111   }
    1112 }
    1113 
    1114 /**
    11151054 * change the parent category of the given categories. The categories are
    11161055 * supposed virtual.
  • trunk/admin/maintenance.php

    r8765 r11827  
    5959  {
    6060    update_path();
    61     update_average_rate();
     61                include_once(PHPWG_ROOT_PATH.'include/functions_rate.inc.php');
     62    update_rating_score();
    6263    break;
    6364  }
  • trunk/admin/rating.php

    r8728 r11827  
    8686;';
    8787  pwg_query($query);
    88   update_average_rate( $vars['e'] );
     88  update_rating_score( $vars['e'] );
    8989}
    9090
     
    101101
    102102
    103 $query = 'SELECT COUNT(DISTINCT(i.id))
    104 FROM '.RATE_TABLE.' AS r, '.IMAGES_TABLE.' AS i
    105 WHERE r.element_id=i.id'. $page['user_filter'] .
    106 ';';
     103$query = 'SELECT COUNT(DISTINCT(r.element_id))
     104FROM '.RATE_TABLE.' AS r
     105WHERE 1=1'. $page['user_filter'];
    107106list($nb_images) = pwg_db_fetch_row(pwg_query($query));
    108107
     
    132131$available_order_by= array(
    133132    array(l10n('Rate date'), 'recently_rated DESC'),
    134     array(l10n('Average rate'), 'average_rate DESC'),
     133                array(l10n('Rating score'), 'score DESC'),
     134    array(l10n('Average rate'), 'avg_rates DESC'),
    135135    array(l10n('Number of rates'), 'nb_rates DESC'),
    136136    array(l10n('Sum of rates'), 'sum_rates DESC'),
     
    165165       i.file,
    166166       i.tn_ext,
    167        i.average_rate,
     167       i.average_rate                           AS score,
    168168       MAX(r.date)          AS recently_rated,
     169                         ROUND(AVG(r.rate),2) AS avg_rates,
    169170       COUNT(r.rate)        AS nb_rates,
    170171       SUM(r.rate)          AS sum_rates
     
    208209       'U_THUMB' => $thumbnail_src,
    209210       'U_URL' => $image_url,
    210        'AVG_RATE' => $image['average_rate'],
     211                         'SCORE_RATE' => $image['score'],
     212       'AVG_RATE' => $image['avg_rates'],
    211213       'SUM_RATE' => $image['sum_rates'],
    212214       'NB_RATES' => (int)$image['nb_rates'],
  • trunk/admin/themes/default/template/configuration.tpl

    r11589 r11827  
    416416    <li>
    417417      <label>
    418         <span class="property">{'Average rate'|@translate}</span>
     418        <span class="property">{'Rating score'|@translate}</span>
    419419        <input type="checkbox" name="picture_informations[average_rate]" {if ($display.picture_informations.average_rate)}checked="checked"{/if}>
    420420      </label>
  • trunk/admin/themes/default/template/rating.tpl

    r8682 r11827  
    3838  <td>{'File'|@translate}</td>
    3939  <td>{'Number of rates'|@translate}</td>
     40        <td>{'Rating score'|@translate}</td>
    4041  <td>{'Average rate'|@translate}</td>
    4142  <td>{'Sum of rates'|@translate}</td>
     
    4950  <td rowspan="{$image.NB_RATES_TOTAL+1}"><a href="{$image.U_URL}"><img src="{$image.U_THUMB}" alt="{$image.FILE}" title="{$image.FILE}"></a></td>
    5051  <td rowspan="{$image.NB_RATES_TOTAL+1}"><strong>{$image.NB_RATES}/{$image.NB_RATES_TOTAL}</strong></td>
     52        <td rowspan="{$image.NB_RATES_TOTAL+1}"><strong>{$image.SCORE_RATE}</strong></td>
    5153  <td rowspan="{$image.NB_RATES_TOTAL+1}"><strong>{$image.AVG_RATE}</strong></td>
    5254  <td rowspan="{$image.NB_RATES_TOTAL+1}" style="border-right: 1px solid;" ><strong>{$image.SUM_RATE}</strong></td>
  • trunk/include/functions_category.inc.php

    r11512 r11827  
    291291    array(
    292292    array(l10n('Default'), '', true),
    293     array(l10n('Average rate'), 'average_rate DESC', $conf['rate']),
     293    array(l10n('Rating score'), 'average_rate DESC', $conf['rate']),
    294294    array(l10n('Most visited'), 'hit DESC', true),
    295295    array(l10n('Creation date'), 'date_creation DESC', true),
  • trunk/include/functions_rate.inc.php

    r8728 r11827  
    117117  pwg_query($query);
    118118
    119   // update of images.average_rate field
     119  return update_rating_score($image_id);
     120}
     121
     122
     123/* update images.average_rate field
     124  * we use a bayesian average (http://en.wikipedia.org/wiki/Bayesian_average) with
     125C = average number of rates per item
     126m = global average rate (all rates)
     127
     128 * param int $element_id optional, otherwise applies to all
     129 * @return array(average_rate, count) if element_id is specified
     130*/
     131function update_rating_score($element_id = false)
     132{
    120133  $query = '
    121 SELECT COUNT(rate) AS count
    122      , ROUND(AVG(rate),2) AS average
    123   FROM '.RATE_TABLE.'
    124   WHERE element_id = '.$image_id.'
    125 ;';
    126   $row = pwg_db_fetch_assoc(pwg_query($query));
    127   $query = '
    128 UPDATE '.IMAGES_TABLE.'
    129   SET average_rate = '.$row['average'].'
    130   WHERE id = '.$image_id.'
    131 ;';
    132   pwg_query($query);
    133   return $row;
     134SELECT element_id,
     135    COUNT(rate) AS rcount,
     136    SUM(rate) AS rsum
     137  FROM '.RATE_TABLE.'
     138  GROUP by element_id';
     139
     140  $all_rates_count = 0;
     141  $all_rates_avg = 0;
     142  $item_ratecount_avg = 0;
     143  $by_item = array();
     144
     145  $result = pwg_query($query);
     146  while ($row = pwg_db_fetch_assoc($result))
     147  {
     148    $all_rates_count += $row['rcount'];
     149    $all_rates_avg += $row['rsum'];
     150    $by_item[$row['element_id']] = $row;
     151  }
     152
     153  $all_rates_avg /= $all_rates_count;
     154  $item_ratecount_avg = $all_rates_count / count($by_item);
     155
     156  $updates = array();
     157  foreach ($by_item as $id => $rate_summary )
     158  {
     159    $score = ( $item_ratecount_avg * $all_rates_avg + $rate_summary['rsum'] ) / ($item_ratecount_avg + $rate_summary['rcount']);
     160    $score = round($score,2);
     161    if ($id==$element_id)
     162    {
     163      $return = array(
     164        'score' => $score,
     165        'average' => round($rate_summary['rsum'] / $rate_summary['rcount'], 2),
     166        'count' => $rate_summary['rcount'],
     167        );
     168    }
     169    $updates[] = array( 'id'=>$id, 'average_rate'=>$score );
     170  }
     171  mass_updates(
     172    IMAGES_TABLE,
     173    array(
     174      'primary' => array('id'),
     175      'update' => array('average_rate')
     176      ),
     177    $updates
     178    );
     179
     180  //set to null all items with no rate
     181  if ( !isset($by_item[$element_id]) )
     182  {
     183    $query='
     184SELECT id FROM '.IMAGES_TABLE .'
     185  LEFT JOIN '.RATE_TABLE.' ON id=element_id
     186  WHERE element_id IS NULL AND average_rate IS NOT NULL';
     187
     188    $to_update = array_from_query( $query, 'id');
     189
     190    if ( !empty($to_update) )
     191    {
     192      $query='
     193UPDATE '.IMAGES_TABLE .'
     194  SET average_rate=NULL
     195  WHERE id IN (' . implode(',',$to_update) . ')';
     196    pwg_query($query);
     197    }
     198  }
     199
     200  return isset($return) ? $return : array('score'=>null, 'average'=>null, 'count'=>0 );
    134201}
    135202
  • trunk/include/picture_rate.inc.php

    r8728 r11827  
    2929if ($conf['rate'])
    3030{
    31   if ( NULL != $picture['current']['average_rate'] )
     31  $rate_summary = array( 'count'=>0, 'score'=>$picture['current']['average_rate'], 'average'=>null );
     32  if ( NULL != $rate_summary['score'] )
    3233  {
    3334    $query = '
     
    3738  WHERE element_id = '.$picture['current']['id'].'
    3839;';
    39     $row = pwg_db_fetch_assoc(pwg_query($query));
     40                list($rate_summary['count'], $rate_summary['average']) = pwg_db_fetch_row(pwg_query($query));
    4041  }
    41   else
    42   { // avg rate null -> no rate -> no need to query db
    43     $row = array( 'count'=>0, 'average'=>NULL );
    44   }
    45   $template->assign('rate_summary', $row);
     42  $template->assign('rate_summary', $rate_summary);
    4643
    4744  $user_rate = null;
    4845  if ($conf['rate_anonymous'] or is_autorize_status(ACCESS_CLASSIC) )
    4946  {
    50     if ($row['count']>0)
     47    if ($rate_summary['count']>0)
    5148    {
    5249      $query = 'SELECT rate
  • trunk/include/ws_functions.inc.php

    r11756 r11827  
    782782  }
    783783  //------------------------------------------------------------- related rates
    784   $query = '
     784        $rating = array('score'=>$image_row['average_rate'], 'count'=>0, 'average'=>null);
     785        if (isset($rating['score']))
     786        {
     787                $query = '
    785788SELECT COUNT(rate) AS count
    786789     , ROUND(AVG(rate),2) AS average
     
    788791  WHERE element_id = '.$image_row['id'].'
    789792;';
    790   $rating = pwg_db_fetch_assoc(pwg_query($query));
    791   $rating['count'] = (int)$rating['count'];
     793                $row = pwg_db_fetch_assoc(pwg_query($query));
     794                $rating['score'] = (float)$rating['score'];
     795                $rating['average'] = (float)$row['average'];
     796                $rating['count'] = (int)$row['count'];
     797        }
    792798
    793799  //---------------------------------------------------------- related comments
  • trunk/themes/default/js/rating.js

    r7957 r11827  
    7373                                {
    7474                                        var t = gRatingOptions.ratingSummaryText;
    75                                         var args =[result.average, result.count], idx = 0, rexp = new RegExp( /%\.?\d*[sdf]/ );
     75                                        var args =[result.score, result.count, result.average], idx = 0, rexp = new RegExp( /%\.?\d*[sdf]/ );
    7676                                        while (idx<args.length) t=t.replace(rexp, args[idx++]);
    7777                                        gRatingOptions.ratingSummaryElement.innerHTML = t;
  • trunk/themes/default/template/picture.tpl

    r11062 r11827  
    197197{if $display_info.average_rate and isset($rate_summary)}
    198198        <tr id="Average">
    199                 <td class="label">{'Average rate'|@translate}</td>
     199                <td class="label">{'Rating'|@translate}</td>
    200200                <td class="value" id="ratingSummary">
    201201                {if $rate_summary.count}
    202202                        {assign var='rate_text' value='%.2f (rated %d times)'|@translate}
    203                         {$pwg->sprintf($rate_text, $rate_summary.average, $rate_summary.count)}
     203                        {$pwg->sprintf($rate_text, $rate_summary.score, $rate_summary.count, $rate_summary.average)}
    204204                {else}
    205205                        {'no rate'|@translate}
Note: See TracChangeset for help on using the changeset viewer.