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 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,

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

    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/

  • I'm thinking using WP_Query and then doing the rest of the work in PHP is going to be more efficient than trying to write a massive SQL statement.
    – dhuyvetter
    Mar 10, 2017 at 11:31
  • This is the formula you are using: Bayesian average ? If not, could you provide it in mathematical form. Thanks
    – Laxmana
    Mar 10, 2017 at 12:09
  • Also how many contestants do you expect?
    – Laxmana
    Mar 10, 2017 at 12:11
  • The calculatoin is based on this: blog.ekini.net/2013/08/18/… which is probably the same as what you are referring to. This is a competition for a big magazine, so hundreds if not thousands of contestants.
    – dhuyvetter
    Mar 10, 2017 at 12:14
  • I think this type of calculation is better to be done by the DB. It is faster especially if you have a lot of entries. More details here: Doing calculations in MySQL vs PHP
    – Laxmana
    Mar 10, 2017 at 12:28

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">
            <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>
        global $post;
        while ($loop->have_posts()) :
            $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); ?>
            <td><a href="<?php echo get_permalink(); ?>" target="_blank"
                   title="<?php echo $title; ?>"><?php echo $title; ?>
                <?php echo $post->ratings_average; ?>
                <?php echo $post->ratings_count; ?>
                <?php echo round($bayesian_average, 3); ?>
        endwhile; ?>
        _e('No Entries', 'textdomain');
  • You can get the $number_of_entrants like $loop->found_posts
    – Laxmana
    Mar 10, 2017 at 13:37
  • I actually used $loop->post_count, updated the code. The array_sum($loop->ratings_count) and other array_sum() don't work though, so i need to find another way to get $total_ratings and other sums.
    – dhuyvetter
    Mar 13, 2017 at 5:48
  • I ended up just looping trough the WP_Query with a foreach, which gives me the results I would expect from a Beyesian average.
    – dhuyvetter
    Mar 13, 2017 at 7:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.