Support » Fixing WordPress » Order by meta_key where meta_value is NUMBER

  • Resolved stepasyuk

    (@stepasyuk)


    Hello all! How to resolve the next problem?
    I have different numbers (prices) in post_meta field and I want to sort posts by this numbers. Problem is that type of this field is ‘LONGTEXT’ but not ‘INT’ or ‘FLOAT’ and sorting of posts occurs at the stage of processing the SQL-REQUEST. As result I have not correctly degree of posts/pages. (like 200, 30, 4, 57 but i want 4, 30, 57, 200) I can’t change the type of field, but I want to sort posts with PHP-code. I didn’t found any plugin, that can resolve this trouble. Can someone tell what to do in this case?

    P.S. Please, do not be surprised my English — I from Ukraine)

Viewing 15 replies - 1 through 15 (of 17 total)
  • Thread Starter stepasyuk

    (@stepasyuk)

    I solved this problem on its own – just add the line

    if (strpos($orderby,’meta_value’) && $q[‘meta_key’] == ‘price’) {$orderby = str_replace(‘meta_value’,’meta_value+0′,$orderby);} else {}

    in wp-includes/query.php (position 2240). The key part here, a string meta_value+0, which forces MySQL to perceive the data as the number of.

    Ho thanks, work fine

    There were some slight changes in WordPress version 2.8.4. Below are the changes I made to make this work for NUMBERS. Stepasyuk you have it exactly right, the way for mysql to return a NUMBER value is to add 0 to the query. The following is what I did, so that it plays nice with pagenavi code, and i presume all plugin code…

    I used query_posts(); to order my posts by meta_key and meta_value ‘value-pairs’. In this example, it works for page view plugins.

    query_posts('meta_key=views&orderby=meta_value_number&order=DESC');
    Then use the LOOP as normal…

    inside query.php I did the following:

    goto line ~2035, add below – $allowed_keys[] = 'meta_value_number';

    goto line ~2057, after break; add below –
    'meta_value_number':
    $orderby = "$wpdb->postmeta.meta_value+0";
    break;

    thats it! Now you can order posts by meta_value NUMBERS!!! YAY!!

    Note to WordPress: You may want to consider adding this functionality by default…

    @gabrieljones / @stepasyuk – I ran into this exact problem and implemented the meta_value_number (+0) solution. EXCELLENT, it is working perfectly. I agree that this functionality should be added to the WordPress core. +100

    gabrieljones, thanks for the hack….it worked perfectly and saved me tons of time!

    Anybody figure out a way to do this without having to hack the core files? Possibly by adding some code to the functions.php file, maybe?

    It solved my great headache. Thanks a lot for the solution.

    In 2.9.1 you need to add:

    $allowed_keys[] = 'meta_value_number'; on line 2005

    and

    case 'meta_value_number':
    $orderby = "$wpdb->postmeta.meta_value+0";
    break;

    on line 2032 in wp-includes/query.php to get this to work 🙂

    thanks for your original answer gabriel, it really helped!

    Hi, I have WP 2.7 and have to sort posts by price in required categories. I have template pages which show posts from those categories. I tested Stepasyuk’s code but it didn’t work for me, then I tested something of my own: added `query_posts(‘cat=categoryID&meta_key=price&orderby=m
    eta_value_number&order=DESC&showposts=’.$limit.’&pag
    ed=’ . $paged); ` to one of my template pages, but it didn’t work too. Can anyone help, please? I’m a newbie in programming and php…

    My problem has been solved!

    This worked perfectly.

    I have a couple of questions:

    1- Is there a way to do what james-mountford described without modifying the query.php file? IE – the functions.php file of a theme?

    2- Anyone know if meta_value_number will be auto supported in future versions of WP?

    Thanks,
    John

    Dan

    (@stencil)

    Hi, I recently submitted a patch for this and it’s been committed to trunk so should appear shortly – no more need to hack the core 🙂

    meta_value_num http://core.trac.wordpress.org/ticket/10649

    Murali Kumar

    (@amuralikumar)

    cant wait to see it in next wordpress version..

    mpvanwinkle77

    (@mpvanwinkle77)

    Anyone have any ideas about how to overcome this problem using a SELECT query. I’ve tried the CAST AS function with no luck.

    i’m on WP 2.9.2 and it doesn’t seem to work here….

    i use this (new WP) query:

    cat=-1,-24&showposts=5&meta_key=views&orderby=meta_value&order=desc&monthnum=4

    and then use the loop (not a foreach)

    example: http://documentingstyle.com/?theme=Code (top, center, most read items)

Viewing 15 replies - 1 through 15 (of 17 total)
  • The topic ‘Order by meta_key where meta_value is NUMBER’ is closed to new replies.