Wordpress - WP Query orderby meta key natural sort?
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();
}