WP Query orderby meta key natural sort?

admin管理员组

文章数量:1333188

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

Share Improve this question asked Sep 8, 2014 at 16:42 bestprogrammerintheworldbestprogrammerintheworld 1,32122 gold badges1919 silver badges4040 bronze badges

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 7

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;
}

本文标签： WP Query orderby meta key natural sort