Order Posts via

A request we are getting more often is to show a list of posts, to elevate some of those posts above others, and to show the posts in a random order. Imagine a post type called "Sponsors". Sponsors are tiered, like "Platinum", "Gold", "Silver", etc. We want the Platinum sponsors to appear before Gold, Gold before Silver, and so on. We don't want to favor one particular Platinum sponsor though, we want them to be randomized but ordered by the tier.

The number one rule we had for implementing this feature is that we could not break existing WordPress functionality. Meaning, pagination has to work, and any sort of post filtering that existed on the site must also continue to work. An additional requirement is that we could not have duplicate posts show up as we paginated results. Hence, pseudo random.

This is achieved by ordering the result set by a weight value (aka the tier) and then to randomize those results, using the MySQL RAND() method. MySQL's RAND() method takes an optional $SEED parameter. If we pass the same $SEED value to our query for each request we can maintain the same random order as we paginate through posts. This ensures that we do not have duplicates. I am generating the seed value using "date('ymd')". The value will change daily, and create a new randomness to the posts each day. Weight is derived from the tier that the posts are assigned. In my case, we use ACF and an ACF field that allows a user to select a single value from a Tier taxonomy. Knowing this, I used the postmeta value of the term id that is selected to get the slug of the term itself. I then used a CASE statement in my query to assign a weight value based on the slug of the selected taxonomy term. Tier1 is assigned 1, tier2 is assigned 2, if there is no term, weight is 9999 (so that these posts always show up after a tiered post). The CASE statement looks like:

SELECT wp_posts.*, CASE weightterms.slug WHEN 'tier1' THEN 1 WHEN 'tier2' THEN 2 ELSE 9999 END AS weight FROM wp_posts ...

In order for this to work we need to JOIN the wp_terms table based on the metavalue of the selected tier taxonomy.

LEFT JOIN wp_postmeta as weightmeta ON (weightmeta.post_id = wp_posts.ID AND weightmeta.meta_key = "sk_tier")
LEFT JOIN wp_terms as weightterms ON (weightmeta.meta_value = weightterms.term_id)

The query basically looks like this when it is compiled (this is a simplified example of how the resulting MySQL query is going to look):

SELECT
    wp_posts.*,
    CASE weightterms.slug WHEN 'tier1' THEN 1 WHEN 'tier2' THEN 2 ELSE 9999 END AS weight
FROM
    wp_posts
LEFT JOIN wp_postmeta as weightmeta ON (weightmeta.post_id = wp_posts.ID AND weightmeta.meta_key = "sk_tier")
LEFT JOIN wp_terms as weightterms ON (weightmeta.meta_value = weightterms.term_id)
WHERE wp_posts.post_type = 'sponsors'
ORDER BY
    weight ASC,
    RAND(20170725);

The goal is to make WordPress write this query for us in the loop. We can do this using filters that modify the WP_Query. The first thing we need to do is to be able to identify the WP_Query so that we do not alter _other_ queries on the site. We only want to change the query that loads posts from our custom Sponsors post type. To do this we add a custom query_var to WordPress and then check for that query_var in our wp_query. Add the query var:

add_filter( 'query_vars', array( $this, 'theme_query_vars_filter' ), 10, 2 );
function theme_query_vars_filter( $qvars ) {
    $qvars[] = 'is_pseudorandom_query';
    return $qvars;
}

We now inject this param into our main query using "pre_get_posts". We do not want our listing of sponsor posts in the admin area of WordPress to be ordered randomly, so we need to check that we are not is_admin().

add_filter( 'pre_get_posts', 'theme_pre_get_posts', 10, 2 );
function theme_pre_get_posts( $wp_query ) {
    if ( !is_admin() && isset( $wp_query->query['post_type'] ) && $wp_query->query['post_type'] == 'sponsors' ) {
        $wp_query->set( 'is_pseudorandom_query', true );
    }
}

This function checks the wp_query object passed to it. If the post type is our custom post type we set the "is_pseudorandom_query" query var to true. With this set we can now setup our wp_query filters. If you are using a custom WP_Query object you can pass "is_pseudorandom_query" as one of the $args:

$my_custom_query = new \WP_Query( [ "is_pseudorandom_query" => true ] );

Now to the WP_Query filters. The three filters we need are posts_fields to add our CASE statement, posts_join to add our custom LEFT JOINS, and posts_orderby to order by our new weight value and then by RAND().

add_filter( 'posts_fields', 'theme_sponsor_posts_fields', 10, 2 );
add_filter( 'posts_join', 'theme_sponsor_posts_join', 10, 2 );
add_filter( 'posts_orderby', 'theme_sponsor_posts_orderby', 10, 2 );

The functions:

function theme_sponsor_posts_fields( $select, $wp_query ) {
    if ( $wp_query->get( 'is_pseudorandom_query' ) == true ) {
        $select .= ", CASE weightterms.slug WHEN 'tier1' THEN 1 WHEN 'tier2' THEN 2 ELSE 9999 END AS weight";
    }
    return $select;
}

function theme_sponsor_posts_join( $join, $wp_query ) {
    if ( $wp_query->get( 'is_pseudorandom_query' ) == true ) {
        $join .= ' LEFT JOIN wp_postmeta as weightmeta ON (weightmeta.post_id = wp_posts.ID AND weightmeta.meta_key = "sk_tier")';
        $join .= ' LEFT JOIN wp_terms as weightterms ON (weightmeta.meta_value = weightterms.term_id)';
    }
    return $join;
}

function theme_sponsor_posts_orderby( $orderby, $wp_query ) {
    if ( $wp_query->get( 'is_pseudorandom_query' ) == true ) {
        $orderby = 'weight ASC, RAND(' . date('ymd') . ') ASC';
    }
    return $orderby;
}

In each function we inspect the passed $wp_query object to see if "is_pseudorandom_query" is set. If it is, then we modify the query.

And there it is. We can now order posts by tier, and then randomize each tier.