# Calculating Bayesian average for custom post type

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

• 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. 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 Mar 10, 2017 at 12:09
• Also how many contestants do you expect? 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. 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 Mar 10, 2017 at 12:28

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;
foreach (\$loop->posts as \$query_post) {
\$count = \$query_post->ratings_count;
\$average = \$query_post->ratings_average;
\$total_ratings += \$average;
}
\$average_rating = \$total_ratings / \$number_of_entrants;
if (\$loop>have_posts()):
?>
<table class="wp-list-table widefat fixed striped pages">
<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>
<tbody><?php
global \$post;
while (\$loop->have_posts()) :
\$loop->the_post();
\$title = get_the_title();
\$this_avg_rating = \$post->ratings_average;
update_post_meta(get_the_ID(), 'bayesian_average', \$bayesian_average); ?>
<tr>
<td><a href="<?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;
``````
• You can get the `\$number_of_entrants` like `\$loop->found_posts` 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. 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. Mar 13, 2017 at 7:09