WordPress order by total score and closest number

Question

I'm working on a WordPress plugin where teams should be orderby their total score and for second parameter who is closest to a random number in a table.

So lets say that there are three teams that has similar total score, and we need to find out who actually did best in the rounds. So first round everyone guess for a random number, eg.: 301020.

• Team 1 guessed: 300000. 1020 away from correct answer
• Team 2 guessed: 28000. 273020 away from correct answer
• Team 3 guessed: 302000. 980 away from correct answer

So Team 3 should be on top, because they was closest to the correct answer.

This is what I have currently:

new WP_Query( array(
      'post_type'       => 'team',
      'posts_per_page'  => -1,
      'order'           => 'DESC',
      'meta_key'        => 'total_score',
      'meta_query'      => array(
         'relation'     => 'AND',
         array(
            'key'       => 'total_score',
            'value'     => '0',
            'compare'   => '>',
            'type'      => 'numeric'
         ),
         'entry_round'  => array(
            'relation'  => 'OR',
            array(
               'key'       => 'round_entry',
               'value'     => 301020,
               'type'      => 'numeric',
               'compare'   => '>='
            ),
            array(
               'key'       => 'round_entry',
               'value'     => 301020,
               'type'      => 'numeric',
               'compare'   => '<='
            )
         ),
      ),
      'orderby'         => array(
         'entry_round'  => 'DESC'
      )
   ));

Answer 1

I think I understand what you're aiming for and it's not possible by just using WP_Query with an array of arguments.

Fortunately, WP provides filters to manipulate the auto generated SQL that WP_Query uses to find the posts.

This should work, I believe:

$randomNumber = 301020;
function orderby_closest($orderby) {
    global $randomNumber;
    return "wp_postmeta.meta_value DESC, ABS(mt1.meta_value - $randomNumber) ASC";
}

add_filter("posts_orderby", "orderby_closest", 10, 1);

$info = new WP_Query( array(
    'post_type'       => 'team',
    'posts_per_page'  => -1,
    'meta_query'      => array(
        'relation'     => 'AND',
        array(
            'key'       => 'total_score',
            'value'     => '0',
            'compare'   => '>',
            'type'      => 'numeric'
        ),
        array(
            'key'       => 'round_entry',
            'compare'   => 'EXISTS'
        ),
    ),
));
remove_filter("posts_orderby", "orderby_closest");

print $info->request;

I've removed the conditions on round_entry, because they don't matter, we just need a join for that field to happen (so we can use it our ORDER BY expression), and we can enforce that by using "EXISTS" for compare. order and orderby have been removed as we will take care of those our self. The magic happens in the function orderby_closest, which is used with the built in posts_orderby filter that is designed for occasions like this one where you want to alter the SQL before it is sent to the database.

As for the return value of orderby_closest, that's pretty simple: First order by total_score, then ABS will return the absolute value of a number, essentially stripping the mathematical sign from the result of the substraction (for our purposes, that's exactly the distance between the guess and the random number). mt1.meta_value is the value of the meta field round_entry (you will have to adapt this if you change the order of the meta_query entires). And we'll sort ascending because you want those with the smallest difference to the random number to be at the top.

print $info->request outputs the generated SQL, which will be something like

SELECT wp_posts.* FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id ) WHERE 1=1 AND ( ( wp_postmeta.meta_key = 'total_score' AND CAST(wp_postmeta.meta_value AS SIGNED) > '0' ) AND mt1.meta_key = 'round_entry' ) AND wp_posts.post_type = 'team' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value DESC, ABS(mt1.meta_value - 301020) ASC

Et voila, we have a lucky winner (or winners).