I have this code:

$args = array(
    'posts_per_page' => -1,
    'post_type' => 'product',
    'meta_key'   => 'custom key',
    'orderby'    => 'meta_value',
    'order'      => 'ASC',        
    'no_found_rows' => true,
    'cache_results' => false,
    'include_children' => false,
    'tax_query' => array(
        array(
            'taxonomy' => $taxonomy,
            'field' => 'id',
            'terms' => array($cat_id)
        )
    )
 );  

$loop = new WP_Query($args);    

When the custom key is like

FA3
FA1
FA10

the wp query orderby paramaters sorts this as

FA1
FA10
FA3

when I want a nsort-algoritm:

FA1
FA3
FA10

Is there any way of acheiving this with WP_Query? (Or is the only option to create an array and sort that array with nsort())

I have this code:

$args = array(
    'posts_per_page' => -1,
    'post_type' => 'product',
    'meta_key'   => 'custom key',
    'orderby'    => 'meta_value',
    'order'      => 'ASC',        
    'no_found_rows' => true,
    'cache_results' => false,
    'include_children' => false,
    'tax_query' => array(
        array(
            'taxonomy' => $taxonomy,
            'field' => 'id',
            'terms' => array($cat_id)
        )
    )
 );  

$loop = new WP_Query($args);    

When the custom key is like

FA3
FA1
FA10

the wp query orderby paramaters sorts this as

FA1
FA10
FA3

when I want a nsort-algoritm:

FA1
FA3
FA10

Is there any way of acheiving this with WP_Query? (Or is the only option to create an array and sort that array with nsort())

• wp-query
• sort

3 Answers 3

No, not as a straight WP_Query. This is because of several factors, with the main one being that mySQL doesn't natively have a natural sort for alphanumeric columns.

WordPress does support a "meta_value_num" for the orderby parameter, which causes it to cast the values to numeric to perform the sort, and this works when you're putting wholly numeric data in the meta_value's, but since you have a mixed set of text and numbers, you can't use that.

You can add a filter to the posts_orderby and rewrite the orderby clause directly to make it whatever you like. If your data is always going to start with "FA", then you could make your filter return something like this:

LENGTH(meta_value) ASC, meta_value ASC

That first sorts by the length, then by the value. So shorter items come first, giving you a semi-natural sort.

In case someone happens to stumble upon this I'll give a you solution, that uses "faked natural sorting" (thanks to Otto) but it also has sorting by multiple custom fields.

function orderbyreplace($orderby ) {
    global $wpdb;
    $new = str_replace($wpdb->prefix.'postmeta.meta_value ASC', 'LENGTH(mt1.meta_value) ASC, mt1.meta_value ASC, mt2.meta_value ASC', $orderby); 
    return $new;
}

function get_posts_by_tax_cat($taxonomy, $parent_cat_id) {
    $args = array(
        'posts_per_page' => -1, 
        'post_type' => 'product',
        'orderby' => 'meta_value',
        'order' => 'ASC',
        'meta_key' => 'flansoppning',     
        'meta_query' => array(
            array(
                'key' => 'flansoppning',
                'value' => '',
                'compare' => 'LIKE'
        ),
        array(
                'key' => 'gangstorlek_mm',
                'value' => '',
                'compare' => 'LIKE'
        )
    ),
    'no_found_rows' => true,
    'cache_results' => false,
    'include_children' => false,
    'tax_query' => array(
        array(
            'taxonomy' => $taxonomy,
            'field' => 'id',
            'terms' => array($parent_cat_id)
        )
    )
  );

//Filter makes it possible to sort on flansoppning (length() gives natural sort) first and then gangstorlek_mm
add_filter('posts_orderby','orderbyreplace');
$loop = new WP_Query( $args );
remove_filter('posts_orderby','orderbyreplace');

$posts = $loop->posts;

if(!empty($posts)) {
        return $posts;
}
return array();
}

Complementing the Otto's answer, if you need to modify query for only product post_type, try this:

add_filter( 'posts_orderby', 'modify_posts_orderby', 10, 2 );
function modify_posts_orderby( $orderby_statement, $wp_query ) {
    // do not modify queries in the admin
    if( is_admin() ) { 
        return $orderby_statement;
    }

    // only modify queries for 'product' post type
    if( $wp_query->get( 'post_type' ) === 'product' ) {
        $orderby_statement = "LENGTH(meta_value) DESC, meta_value DESC";
    }
    return $orderby_statement;
}