0

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!

2 Answers 2

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',
);
3
  • Thank you for your reply S. I actually have the title in a SKU meta field too. Would you mind guiding me in the right direction with this solution?
    – Keith
    Jul 29, 2015 at 22:00
  • Please see the edit. I am guessing a bit.
    – s_ha_dum
    Jul 29, 2015 at 22:06
  • 1
    Er, I think "Z" usually comes after "U"...!
    – bonger
    Jul 30, 2015 at 7:08
1

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.)

2
  • Thanks so much for your reply. Doesn't seem to work unfortunately. It returns no products.
    – Keith
    Jul 31, 2015 at 8:56
  • Oh, is it that the query returns posts without the filter but doesn't with it?
    – bonger
    Jul 31, 2015 at 15:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.