Multiple Orderby's using random order WP_Query

Question

Here is the setup.

The site in question is a website that displays listings. Each listing has a rank (1-3). Basically it's a listing type... small, medium & large to determine how much the customer pays.

When I do a WP_QUERY of all of the listings I want to display them in order DESC from 3-1 (larget - small)... basically show all large listings, them medium then small listings.

Think sortfolio.com

The problem is not only do I want to order them large-small, but I also want to randomize each listing so the same large one will not always be the first one displayed.

Here is what I was trying:

$query = new WP_Query( array( 'post_type' => 'listing', 'orderby' => 'meta_value_num rand', 'meta_key' => 'membership'));

As you can see I am ordering them by the membership level but then am trying to randomizing them. It seems to work, but it always puts the initial membership results in ASC order and there is no way (i can figure) around it.

Any help is greatly appreciated!!

Answer 1

just add, 'order' => 'DESC'

$query = new WP_Query( array( 'post_type' => 'listing', 'orderby' => 'meta_value_num rand', 'meta_key' => 'membership', 'order' => 'DESC'));

or am I missing something here?

Answer 2

I ran your query and got ...

ORDER BY wp_postmeta.meta_value+0,RAND() DESC

... which is what I expected. If I am reading your question correctly, what you want is ...

ORDER BY wp_postmeta.meta_value+0 DESC,RAND()

... as in this question at SO. That should sort by meta_value first and then randomize any second level ordering that is possible.

WP_Query won't do that. You can see in the source that the order "direction" is tacked onto the end of the order by string. It always ends up in the wrong place for you.

To make this work you will need to create a filter for posts_orderby.

function alter_order_wpse_62468($orderby) {
  global $wpdb;
  remove_filter('posts_orderby','alter_order_wpse_62468');
  return "{$wpdb->postmeta}.meta_value+0 DESC, RAND()";
}
add_filter('posts_orderby','alter_order_wpse_62468');
$query = new WP_Query( array( 'post_type' => 'listing', 'orderby' => 'meta_value_num rand', 'meta_key' => 'membership'));
var_dump($query->request);

$query = new WP_Query( array( 'post_type' => 'listing', 'orderby' => 'meta_value_num rand', 'meta_key' => 'membership'));
var_dump($query->request); die;

If you add your filter exactly like that, just before your query, it will run and then remove itself so that it doesn't mess with any other queries. You can see that by looking at the var_dumps of the two queries (which is why I ran the query twice). What matters it the add_filter part. The function itself can be defined elsewhere.

If you add the filter somewhere else, you will need to add some logic the callback to control when and where it runs, or it will alter other queries. For reference, here is one technique for adding that logic.

If the filter needs to run in different places with different arguments you may need to regex the existing ORDER BY clause to preserve whatever is already there.