2

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/

  • 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 '17 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 '17 at 12:09
  • Also how many contestants do you expect? – Laxmana Mar 10 '17 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 '17 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 '17 at 12:28
0

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="<?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 – Laxmana Mar 10 '17 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 '17 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 '17 at 7:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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