[resolved] Sort posts by custom field numeric value (12 posts)

  1. Chad Bush
    Posted 6 years ago #

    I'm trying to customize a template category page that sorts posts within a certain category by the natural numeric order specified in a custom field. I have a realtor's website, we're using a post for each listing, and they want to have all the listing posts sort by price. I've created a custom field called 'price' and typed a number into each field.

    Using the instructions here http://codex.wordpress.org/Displaying_Posts_Using_a_Custom_Select_Query I'm able to do this, mostly, but the posts are not in natural numeric order. The custom field value is a string, and PHP does not sort the values numerically by default.

    I'm a bit of a novice with PHP, but very experienced with custom templates. Please help...

  2. Mark / t31os
    Posted 6 years ago #

    Can i see what you have so far?

    I can help expand on what you have, saving writing something from scratch..


  3. Chad Bush
    Posted 6 years ago #

    Thanks for the response! I just somehow found the answer by casting about blindly on Google. I'll post it here in case it might help others. Originally, I was trying to use this code:

    $querystr = "
       SELECT wposts.*
       FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
       WHERE wposts.ID = wpostmeta.post_id
       AND wpostmeta.meta_key = 'price'
       AND wposts.post_type = 'post'
       ORDER BY wpostmeta.meta_value DESC

    The values returned by the database would not sort naturally. So, I simply changed the last line to this:

    ORDER BY wpostmeta.meta_value+0 DESC

    and now it does what I want it to. Perhaps if you see anything else I might be missing here, I'd appreciate your input... This level of hacking templates is as deep as I've gone.

  4. Mark / t31os
    Posted 6 years ago #

    See this thread for making the code a little easier..

    The information there should be relevant in helping a little...

    Hard to say regarding the sort order because i don't have your data infront of me to fiddle around with (i'm not asking for it), so i can only guess...

    In any case whenever you have data in an array you can resort that data many different ways depending on what PHP function you call...

    and more...

    There's also quite good support for meta value sorting built into WordPress if you dig through the codex.. ( i know finding specific info can sometimes be tough ).

    Since data is usually returned in an array your options are quite open...

    If you have the behaviour you want now though, great!... :)

    Have a good weekend.. ;)

  5. pl4y312
    Posted 6 years ago #

    Is there anyone know where I should put that script?

  6. cruisetastic
    Posted 6 years ago #

    I can't get them to sort beyond the FIRST number... so if there's a property $24,000 and another $399,000 they'll be sequential. Any suggestions? Here's the code I'm using:

    $price = get_post_meta($post->ID, 'price', true);
    $querystr = "
    SELECT * FROM $wpdb->posts
    LEFT JOIN $wpdb->postmeta ON($wpdb->posts.ID = $wpdb->postmeta.post_id)
    LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
    LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
    WHERE $wpdb->term_taxonomy.term_id = 14
    AND $wpdb->term_taxonomy.taxonomy = 'category'
    AND $wpdb->posts.post_status = 'publish'
    AND $wpdb->postmeta.meta_key = 'price'
    ORDER BY $wpdb->postmeta.meta_value+0 DESC

    $pageposts = $wpdb->get_results($querystr, OBJECT);


  7. s_ha_dum
    Posted 6 years ago #

    I can't get them to sort beyond the FIRST number... so if there's a property $24,000 and another $399,000 they'll be sequential.

    I don't think I understand. The first number should follow the second assuming no values in between, since you are ordering with DESC. What isn't sorting right? Do you get your results the other way around?

  8. saranghills
    Posted 6 years ago #

    My client wants the posts to be sorted with a custom number value. As you all have said, it counts 10 as 1. Can anyone tell me how to make WP understand that 10 is 10, not 1 ?

    Here is the code that I am using, if it will be of any help:


    I tried searching for a solution, but couldn't find any good results.

  9. s_ha_dum
    Posted 6 years ago #

    Did you try ravenseye's trick?

    ORDER BY wpostmeta.meta_value+0 DESC

  10. cassio
    Posted 6 years ago #

    I changed the file query.php to see which custom field was passed.

    If the field is numeric, I use the solution below:
    $orderby = "$wpdb->postmeta.meta_value+0"

    Otherwise I use the default WordPress:
    $orderby = "$wpdb->postmeta.meta_value"

    But now I face a very similar problem: if the field is a date, what do I do?

  11. Adrian Diaconescu
    Posted 6 years ago #

    Ok, I've been pulling my hair for the last couple of hours trying to figure this out and this half-finished post was my only lead. :)

    Then I started thinking this was more of an SQL issue, so I did some research and here's what I found:

    1. If you want to sort by a numerical custom filed, use the code ravenseye provided, but insted of the +0 hack, use this:

    ORDER BY ABS(wpostmeta.meta_value) DESC

    If you also have a $ sign in front, like I had, use this:

    ORDER BY ABS(SUBSTRING(wpostmeta.meta_value, 2)) DESC

    This ignored the first character, so to speak. So $121.45 becomes 121.45.

    If you have something like a product code #PRK4531 and want to compare those, use the SUBSTRING function with 5 as the second parameter.

    Hope this helps!

  12. Joezy
    Posted 6 years ago #

    Pulling my hair out too, Rubiqube!

    Am trying to sort posts using a 'size' custom field which shows values in square feet. Currently my code looks like this:

    <?php query_posts($query_string . '&meta_key=size&orderby=meta_value'); if (have_posts()) : while (have_posts()) : the_post(); ?>

    but it's not sorting the posts.

    I'd be very grateful if you could show me what the code should look like if it incorporated your revisio. Incidentally, should 'order by' in your post by 'orderby'?

Topic Closed

This topic has been closed to new replies.

About this Topic