Calculating Bayesian average for custom post type

by

I have a custom post type called entries, which holds contestants for a competition. There is a 5 star rating function, the ratings are stored with the entries. So I can get the ratings_average, ratings_count and ratings_sum with a WP_Query:

    $args = array(
        'post_type' => 'entry',
        'orderby' => 'ratings_average',
        'order' => 'DESC',
        'posts_per_page' => 10,
        'post_status' => 'publish'
    );
    $loop = new WP_Query($args);

Now I need to calculate the Bayesian average or mean or estimate (which are just different names for the same thing as far as I can tell), which should be possible with this SQL query:

SELECT

( SELECT COUNT(*) FROM `bayesian_test` ) AS total_entrants,
( SELECT SUM( vote_count ) FROM `bayesian_test` ) AS total_votes,
( (SELECT total_votes) / (SELECT total_entrants) ) AS average_votes,
( SELECT SUM( rating_count ) FROM `bayesian_test` ) AS total_ratings, 
( (SELECT total_ratings) / (SELECT total_entrants) ) AS average_rating,
post_title,

( ( (SELECT average_votes) * (SELECT average_rating) ) + (vote_count * rating_average) ) / ( (SELECT average_votes) + vote_count) AS mean

FROM 
    bayesian_test
ORDER BY 
    mean ASC;

Is there a way to combine SQL statements with WP_Query?

Or what is the best way to get the same results I get from WP_Query in a SQL query I can work with in $wpdb?

The entries are stored by WordPress in the wp_posts with post_type entry and the ratings_count, ratings_sum, and ratings_average are stored in wp_postmeta. So I’d have to write a join to get those and then do the above query on the result. Wouldn’t this be heavy on the DB? This is shown in a Dashboard widget, so the query would run every time someone hits /wp-admin/.

What is the best (as in most efficient) way to tackle this?

The Bayesian rating / average is calculated like this: http://fulmicoton.com/posts/bayesian_rating/

1 Answer
1

This is the (updated) code I’m using, which gives results like I’d expect:

    $args = array(
        'post_type' => 'entry',
        'orderby' => 'bayesian_average',
        'order' => 'DESC',
        'post_status' => 'publish'
    );
    $loop = new WP_Query($args);
    $number_of_entrants = $loop->post_count;
    $total_ratings = $total_num_votes = 0;
    foreach ($loop->posts as $query_post) {
        $count = $query_post->ratings_count;
        $average = $query_post->ratings_average;
        $total_num_votes += $count;
        $total_ratings += $average;
    }
    $average_rating = $total_ratings / $number_of_entrants;
    $avg_num_votes = $total_num_votes / $number_of_entrants;
    if ($loop>have_posts()):
        ?>
        <table class="wp-list-table widefat fixed striped pages">
        <thead>
        <tr>
            <th scope="col" id="entry"><?php _e('Entry', 'textdomain'); ?></th>
            <th scope="col" id="rating-average"><?php _e('Rating average', 'textdomain'); ?></th>
            <th scope="col" id="rating-count"><?php _e('Rating count', 'textdomain'); ?></th>
            <th scope="col" id="beyesian-rating"><?php _e('Bayesian Rating', 'textdomain'); ?></th>
        </tr>
        </thead>
        <tbody><?php
        global $post;
        while ($loop->have_posts()) :
            $loop->the_post();
            $title = get_the_title();
            $this_num_votes = $post->ratings_count;
            $this_avg_rating = $post->ratings_average;
            $bayesian_average =  (($avg_num_votes * $average_rating) + ($this_num_votes * $this_avg_rating)) / ($avg_num_votes + $this_num_votes);
            update_post_meta(get_the_ID(), 'bayesian_average', $bayesian_average); ?>
            <tr>
            <td><a href="https://wordpress.stackexchange.com/questions/259601/<?php echo get_permalink(); ?>" target="_blank"
                   title="<?php echo $title; ?>"><?php echo $title; ?>
                </a></td>
            <td>
                <?php echo $post->ratings_average; ?>
            </td>
            <td>
                <?php echo $post->ratings_count; ?>
            </td>
            <td>
                <?php echo round($bayesian_average, 3); ?>
            </td>
            </tr><?php
        endwhile; ?>
        </tbody>
        </table><?php
    else:
        _e('No Entries', 'textdomain');
    endif;

Leave a Comment