How to order WP_Query to group results?

Question

I have a list of products in WooCommerce that have titles like so:

Please note: The "groups" are just for myself, they are not categories etc. All products are the same type of product, just two types of products have different codes (Z and UK)

"Group 1" - Unique numbers

• 5000
• 4999
• 4998
• 4997
• 4996
• etc etc

"Group 2" - Unique numbers that begin with a Z

• Z5000
• Z4999
• Z4998
• Z4997
• Z4996
• etc etc

"Group 3" - Unique numbers that begin with UK

• UK5000
• UK4999
• UK4998
• UK4997
• UK4996
• etc etc

Obviously by default, WordPress orders by date so on the product's archive page, they look like this:

• UK4020
• 2122
• Z3122
• 4999
• UK1001
• etc etc

What I would like (but can't seem to find any answer with my Google-fu), is to custom order my products like so:

• Unique numbers, in descending order
• Unique numbers that begin with Z, in descending order
• Unique numbers that begin with UK, in descending order

... So my products look like this:

• 5000,
• 4999,
• etc
• Z5000,
• Z4999,
• etc
• UK5000,
• UK4999
• etc

Hope the above makes sense!

Answer 1

If your naming system is consistent then simply ordering by the post name should do it:

$args = array(
    'orderby' => 'title', 
    'order' => 'ASC',
);
$q = new WP_Query($args);
var_dump($q->request);
var_dump(wp_list_pluck($q->posts,'post_title'));

Or, as a pre_get_posts filter (assuming the main query is what we want to alter):

add_action(
  'pre_get_posts',
  function ($qry) {
    if ($qry->is_main_query()) {
      $qry->set('orderby','title');
    }
  }
);

This is what I'd consider a fragile solution. A more complicated one, but more reliable, would be to save custom meta for your IDs and order by that custom meta.

If you have the data saved as custom meta then use these arguments instead:

$args = array(
    'meta_key' => 'SKU', // maybe sku; or whatever your KEY is
    'orderby' => 'meta_value',
    'order' => 'ASC',
);

Answer 2

I think you need a filter on posts_orderby to do this:

add_filter( 'posts_orderby', function ( $orderby, $query ) {
    if ( $query->get( 'orderby' ) == 'zuk' ) {
        global $wpdb;
        //$field = $wpdb->posts . '.post_title';
        $field = $wpdb->postmeta . '.meta_value';
        $order = $query->get( 'order' );
        $orderby = $wpdb->prepare(
            'CASE'
            . ' WHEN LEFT(' . $field . ', 1) = %s THEN CONCAT(%s, LPAD(SUBSTR(' . $field . ', 2), LENGTH(~0), %s))'
            . ' WHEN LEFT(' . $field . ', 1) = %s THEN CONCAT(%s, LPAD(SUBSTR(' . $field . ', 3), LENGTH(~0), %s))'
            . ' ELSE LPAD(' . $field . ', LENGTH(~0), %s)'
            . ' END ' . $order
            , 'Z', 'A', '0', 'U', 'B', '0', '0'
        );
    }
    return $orderby;
}, 10, 2 );

$args = array(
    'meta_key' => 'SKU',
    'orderby' => 'zuk',
    'order' => 'ASC',
);
$query = new WP_Query( $args );

(If your data's number is always 5 digits, then you could just sort based on the length:

add_filter( 'posts_orderby', function ( $orderby, $query ) {
    if ( $query->get( 'orderby' ) == 'zuk' ) {
        global $wpdb;
        //$field = $wpdb->posts . '.post_title';
        $field = $wpdb->postmeta . '.meta_value';
        $order = $query->get( 'order' );
        $orderby = 'LENGTH(' . $field . ') ' . $order . ', ' . $field . ' ' . $order;
    }
    return $orderby;
}, 10, 2 );

but this is obviously a lot flakier.)