Top Images Adapted for Weighted Average & Minimum # Votes for Top 10

[RuthE]

I'd like to have both, if not just the second thing.

I'd like for the top 20 images to have a requirement on how many votes it's received. For say, an image must have the highest rank and at least 15 votes before it will be the top ranked images in top.php. Is this possible?

The other thing is that if images raitings were based on number of votes and the rating of an image. Not important to me if the above is met. Especially since voters can only see the top twenty.

Thanks.

Ruth

[V@no]

I'd like for the top 20 images to have a requirement on how many votes it's received. For say, an image must have the highest rank and at least 15 votes before it will be the top ranked images in top.php. Is this possible?

in the mysql query for rating add AND i.image_votes > 15
for example:

        WHERE i.image_active = 1 AND i.cat_id NOT IN ($cat_id_sql) AND i.cat_id = c.cat_id AND i.image_votes > 15

The other thing is that if images raitings were based on number of votes and the rating of an image. Not important to me if the above is met. Especially since voters can only see the top twenty.

by default there is "top" by votes section...

[RuthE]

Many thanks for your reply. The minimum votes required was quite easy to do.

However, I am still trying to use a 'weighted average' for the pictures, but not to much avail. Here is the formula I've been trying to incorporate for use in 4images.

The formula is also known as True Bayesian Estimate Calculation:

Weighted average = (v / (v+m)) * R + (m / (v+m)) * C

R  =  the regular average for the picture
v  =  number of votes for the picture
m  =  minimum votes required to be listed in the top 10 (currently 20)
C  =  the mean vote across the whole pictures

I've tried a few ways, but the rating never ends up 'right'. It's either way too low or too high. I guess it's just been too long since I've practiced any math skills. Any help would be appreciated.

We've been trying to do it via the new_raiting line in the functions.php file.

[V@no]

C = the mean vote across the whole pictures

what does that mean?

[RuthE]

The mean vote would be the average rating given to all images.

[alekseyn1]

Wow, an old subject, but I am trying to figure this out myself and need help...

I am trying to come up with the proper approach to the ratings system.

At the moment I have a formula listed above working on my site with the following code:

// Rating
//formula used at IMDB for rating of their movies
//
//    v               m 
//------- * R  +  ------- * c 
//  v + m           v + m 
//
//Where:
//v = number of votes
//m = minimum number of votes to be considered (on IMDB = 1250)
//R = average "vote" given to the image
//c = average "vote" for all the image (on IMDB currently = 6.9)

$cat_id_sql = get_auth_cat_sql("auth_viewcat", "NOTIN");

$vote_number = 30; //"m" parameter from the above formula
$cutoff_time = time() - 60 * 60 * 24 * 30;
$time = "AND i.image_date >=".$cutoff_time."";
$time_av = "AND image_date >=".$cutoff_time."";

$sql1 = "SELECT AVG(image_rating) as avg, image_active, cat_id
        FROM ".IMAGES_TABLE."
        WHERE image_active = 1 ".$time_av." AND cat_id NOT IN ($cat_id_sql) AND image_rating <> 0";

$result1 = $site_db->query_firstrow($sql1);
$average_of_all_images = $result1['avg'];

//this is checking if the average rating is 5.0. If so, the system will sort by 
if (($average_of_all_images == 5) || (!$average_of_all_images)) {
$order_by = "image_votes";

} else {

$order_by = "(((i.image_votes/(i.image_votes+".$vote_number."))*i.image_rating)+((".$vote_number."/(i.image_votes+".$vote_number."))*".$average_of_all_images."))";
}

//echo "M = ".$vote_number.". Average of all ".$average_of_all_images."... ";

$sql = "SELECT i.image_id, i.user_id, i.cat_id, i.image_name, i.image_thumb_file, i.image_rating, i.image_votes, c.cat_name, u.user_name, c.hide_author
        FROM ".IMAGES_TABLE." i, ".CATEGORIES_TABLE." c, ".USERS_TABLE." u
        WHERE i.image_active = 1 ".$time." AND i.cat_id NOT IN (".$cat_id_sql.") AND i.cat_id = c.cat_id AND i.user_id = u.user_id ".$cat_match_sql."
        ORDER BY ".$order_by." DESC

LIMIT 10";

but it sometimes gives me weird results still.... especially when I set the cutoff time for 24 hours and M parameter to 3 votes only....
I do not fully understand how the ORDER BY ".$order_by." DESC line works in the context of the task.... does it perform properly? Is there another way (maybe using a temporary table) to list the images based on the formula?

There is another approach to calculating the ratings though that I have not tested yet... it will only work if you have installed the mod for saving the votes to the DB though...

The approach is to calculate a weighted average rating for an image instead of arithmetic average that is being calculated in the standard installation and then just simply sort by rating.
(taken from here)

If you have the mod installed then running this query will give you a weighted average rating for all your images:


//arithmetic mean for all images
SELECT sum( vt ) / sum( cnt )
FROM (
SELECT count( vote ) * count( vote ) AS cnt, vote * count( vote ) * count( vote ) AS vt
FROM 4images_voted
GROUP BY vote
)a



//arithmetic mean for an image
SELECT sum( vt ) / sum( cnt )
FROM (
SELECT count( vote ) * count( vote ) AS cnt, vote * count( vote ) * count( vote ) AS vt
FROM 4images_voted
WHERE image_id = $image_id
GROUP BY vote
)a


I may be breaking my head over nothing, but image ratings are important for my site and I need them working as accurate as possible... So please MySQL gurus, I need help with this. Which approach you think is the best?

in Russian here