Determine article rank based on views of all articles

3

The idea is to determine the reputation of a given article based on views of all existing articles.

An article can have a reputation of 1 to 5 stars, and it is constantly changing based on the views registered for all articles.

The goal is to assign more stars to the most viewed article, knowing that the assigned stars are calculated in real time by viewing them recorded so far in all catalog articles.

MySQL

For this purpose, whenever an article is viewed, the view is recorded in a table:

DESCRIBE 'product__statistic_views' 
┌───────────────┬───────────┬──────┬─────┬─────────────────────┬────────────────┐
│ Field         │ Type      │ Null │ Key │ Default             │ Extra          │
├───────────────┼───────────┼──────┼─────┼─────────────────────┼────────────────┤
│ id            │ int(13)   │ NO   │ PRI │ NULL                │ auto_increment │
├───────────────┼───────────┼──────┼─────┼─────────────────────┼────────────────┤
│ product_id    │ int(13)   │ NO   │ MUL │ 0                   │                │
├───────────────┼───────────┼──────┼─────┼─────────────────────┼────────────────┤
│ session_id    │ char(100) │ NO   │ MUL │ old-na              │                │
├───────────────┼───────────┼──────┼─────┼─────────────────────┼────────────────┤
│ page_views    │ int(13)   │ NO   │ 0   │                     │                │
├───────────────┼───────────┼──────┼─────┼─────────────────────┼────────────────┤
│ date_created  │ datetime  │ NO   │ MUL │ 0000-00-00 00:00:00 │                │
└───────────────┴───────────┴──────┴─────┴─────────────────────┴────────────────┘

Example of a record:

┌────┬────────────┬──────────────────────────────────┬────────────┬─────────────────────┐
│ id │ product_id │ session_id                       │ page_views │ date_created        │
├────┼────────────┼──────────────────────────────────┼────────────┼─────────────────────┤
│ 2  │ 37         │ 153dd95d83bc6a6691a4c3bab42215c9 │ 3          │ 2014-12-02 18:47:17 │
└────┴────────────┴──────────────────────────────────┴────────────┴─────────────────────┘

This tells us that article # 37 has a unique visitor, who even viewed the product 3 times in the same session.

PHP

On the PHP side, the information is rendered as follows:

/**
 * Product Rating
 *
 * Prepare the necessary HTML to present the product
 * rating stats based on their views count while
 * compared to all products views.
 * 
 * @param integer $count Product views count.
 * @param integer $grandTotal Sum of all products views.
 *
 * @return string $returnHtml HTML ready to be used.
 */
public function productRating($count=0, $grandTotal=0) {

    $returnHtml = '
    <span class="fa fa-star-o"></span>
    <span class="fa fa-star-o"></span>
    <span class="fa fa-star-o"></span>
    <span class="fa fa-star-o"></span>
    <span class="fa fa-star-o"></span>';

    if (intval($count)>=1) {

        $rating = number_format( ceil( ( ($count*100) / $grandTotal ) / 20 ), 0);

        if ($rating>0) {

            $returnHtml = '';

            for ($i=0; $i<$rating; $i++) {
                $returnHtml.= '<span class="fa fa-star" data-rating="'.$rating.'"></span>';
            }

            for ($i=5; $i>$rating; $i--) {
                $returnHtml.= '<span class="fa fa-star-o" data-rating="'.$rating.'"></span>';
            }
        }
    }

    return $returnHtml;
}

Usage example:

/* Visualizações são únicas, pelo que o total de visualizações do produto é uma
 * contagem dos registos com o seu ID na tabela em cima, e o total de visualizações
 * de todos os produtos é uma contagem de todos os registos na tabela em cima referida.
 */
$ratingHtml = $this->productRating(1, 10);

HTML

The result will look something like this:

Question

For what has been described, does the function that determines the classification of each product perform its work efficiently?

Can this whole process be simplified?

    
asked by anonymous 02.12.2014 / 19:54

2 answers

2

If I understood the code correctly, this method should generate a very low value for each product, since none will have 100% of the views you pass $grandTotal . An alternative way to calculate would be to compare to the product that has the most views, rather than compare to the sum of all views. In SQL, this could be done like this:

SELECT
  product_id,
  page_views,
  CEIL((page_views / (SELECT MAX(page_views) FROM product__statistic_views)) * 100) AS percentual
FROM product__statistic_views
-- WHERE product_id = ? /* para filtrar por produto se necessário */

link

This method would still have the problem of generating low values for all new products, which generally tend to have few views . You could use some reference date to minimize this. For example, to compare a product with those that were registered up to 7 days before it (considering that date_created is the date of registration):

SELECT
  product_id,
  page_views,
  CEIL((page_views / (SELECT MAX(page_views) FROM product__statistic_views WHERE date_created BETWEEN DATE_SUB(date_created, INTERVAL 7 DAY) AND date_created)) * 100) AS percentual
FROM product__statistic_views
-- WHERE product_id = ? /* para filtrar por produto se necessário */
    
02.12.2014 / 21:21
0

The calculation is perfect, what I believe is wrong is the fact that the $ returnHtml variable is started with stars. In that case, it seems to me that more than 5 stars will appear. So I think the correct one is this:

/**
 * Product Rating
 *
 * Prepare the necessary HTML to present the product
 * rating stats based on their views count while
 * compared to all products views.
 * 
 * @param integer $count Product views count.
 * @param integer $grandTotal Sum of all products views.
 *
 * @return string $returnHtml HTML ready to be used.
 */
public function productRating($count=0, $grandTotal=0) {

    $returnHtml = '';

    if (intval($count)>=1) {

        $rating = number_format( ceil( ( ($count*100) / $grandTotal ) / 20 ), 0);

            for ($i=1; $i<=5; $i++) {
                if($i <= $rating){
                   $returnHtml .= '<span class="fa fa-star" data-rating="'.$i.'"></span>';
                }else{
                   $returnHtml .= '<span class="fa fa-star-o" data-rating="'.$i.'"></span>';
                }
            }    

        }else{
           $returnHtml = '
           <span class="fa fa-star-o"></span>
           <span class="fa fa-star-o"></span>
           <span class="fa fa-star-o"></span>
           <span class="fa fa-star-o"></span>
           <span class="fa fa-star-o"></span>';

        }

    return $returnHtml;
}

I'm assuming that the fa-star-o class is the disabled star, the gray that you showed in the example.

    
02.12.2014 / 20:14