Multiple meta_key ordering with pre_get_posts

Question

I'm trying to order products by multiple meta keys, so that the most selled products appear first and the out of stock products lastly.

I managed to make the out of stock products show in the end, but the most selled are not ordering correctly.

I'm testing in this cenario, 5 products, 2 out of stock (showing in the end), the other 3 are being ordered like this, less selled first, most selled second and the last...

  $meta_query = array('relation' => 'AND');

  $meta_query[] =  array(
    'key' => '_stock_status',
  ); 
  $meta_query[] =  array(
    'key' => 'total_sales',
  );

  $orderby = array(
    '_stock_status' => 'ASC', 
    'total_sales' => 'DESC',       
  );

  $query->set( 'meta_query', $meta_query );
  $query->set( 'orderby', $orderby );

Answer 1

This cannot be done via WP_Query, the orderby parameter can take multiple values, but those are telling it what to order by, aka date, time, title, etc These are the valid values

You can specify that it must order by post meta, but you can't specify more than one post meta, because it uses the meta defined in the meta_key option, which does not take an array, and can only have a single value

To do this you will need to use raw SQL, and even then it may not be possible with a single query

On top of that, this query is extremely expensive and slow, doing this will make it even slower.

---

As an aside, switching _stock_status to use a taxonomy instead of post meta, would provide a big speed up on any queries that use it. Never store something in post meta if you need to search for it or list everything with that key/value pair. That's what taxonomies are for.