Order post by meta value with semicolon

Question

I had a post type music. For which i created custom field Music length. Which store music duration in this format like 01:50 I want to display post on order by Music length. I had use both order by meta_value and meta_value_num. But it does not work. My code to get post is:

<code>
$args = array(
  'post_type' => 'music',
  'meta_key' => 'length',
  'orderby' => 'meta_value_num',
  'order' => 'DESC',
  'posts_per_page' => 10
);
$pop_posts = new WP_Query( $args );
</code>

My question is how to order the post by its meta value in semicolon format(01:50, 02:46, 03:04, 02:37). So the post with meta value 03:04 comes first then 02:46 and so on! Is there any way?

See this refer https://stackoverflow.com/questions/6158726/php-compare-time ans this -- https://stackoverflow.com/questions/36609349/compare-the-current-date-to-a-timestamp-in-wordpress-with-meta-query — softbrewery, Jun 12 '17 at 07:34

score 1 · Answer 1 · answered Jun 12 '17 at 07:42

1

Use following code

$args = array(
  'post_type' => 'music',
  'meta_key' => 'length',
  'orderby' => "REPLACE(meta_value_num, ':', '')",
  'order' => 'DESC',
  'posts_per_page' => 10
);
$pop_posts = new WP_Query( $args );

But note that it will be very slow for large datasets as it has to recompute the new string for every row.

answered Jun 12 '17 at 07:42

pravindot17

1,199
1
15
32

'orderby' => "REPLACE(meta_value_num, ':', '')", does not work – maddy Jun 12 '17 at 09:22
did you get any error? As I run the same query with same data structure – pravindot17 Jun 12 '17 at 09:32
it didn't show any error. It display all posts but not in proper order. – maddy Jun 12 '17 at 09:36
where did you get this code 'orderby' => "REPLACE(meta_value_num, ':', '')" the REPLACE method. Is there any documentation – maddy Jun 12 '17 at 09:37
can you post your raw sql query here – pravindot17 Jun 12 '17 at 09:55

Order post by meta value with semicolon

1 Answers1