WordPress posts orderby meta value with strings and numbers

Question

I have a custom field (advanced custom field) called custom_order and I want WordPress to sort all posts by the field values.

The custom_order field's values can be written in 3 ways:

• numbers only (3, 400, 6424)
• numbers, then strings (3A, 3B, 5000A)
• numbers, then strings, then numbers (3A1, 3A2, 401AZ1, 9000A3, 9000A1)

More examples of custom_order values:

Wanted order:

1, 2, 3, 3A, 3B, 3C, 3CA, 4, 400, 401, 401A, 401AZ, 401Z, 9000, 9000A, 9000A1, 9000A3

Conditions:

• 3 should come before 200 (numeric order)
• 3 should come before 3A (number and strings)
• 3A should come before 3A1 (numbers, strings, numbers)

---

My try:

My current code is sorting by numbers (condition 1), but ignores condition 2 and condition 3.

Therefore, my code gives me this order:

1, 2, 3A, 3, 3C, 3B, 4 <-- all 3's are not ordered, which is bad

The code I'm using (in functions.php):

add_action('pre_get_posts', function ($q) {
    if (
        !is_admin() // Target only front end queries
        && $q->is_main_query() // Target the main query only
        && ($q->is_search() || $q->is_post_type_archive('data-base'))
    ) {
        $q->set('meta_key', 'custom_order');
        $q->set('order',    'DESC');
        $q->set('orderby',  'meta_value_num');
    }
});

Answer 1

I found a neat solution!

$q->set(
    'meta_query',
    array(
        'relation' => 'OR', //**** Use AND or OR as per your required Where Clause
        'clause1' => array(
            'key'     => 'custom_order',
            'compare' => 'EXISTS',
        ),
    )
);
$q->set(
    'orderby',
    array(
        'meta_value_num'  => 'ASC', // this reorders by numbers
        'meta_value'  => 'ASC', // this reorders by strings after sorting numbers
    )
);