query_posts: sorting by custom field numerically NOT alphanumerically

  • I need some guidance with sorting posts by custom field. I’ve read numerous similar posts here on www.remarpro.com, I’ve been on other sites… lets get to it…

    I’m attempting to sort posts by a custom field using query_posts().

    query_posts($query_string.'&meta_key=base_price&orderby=meta_value');

    The custom field (base_price) is a numeric value that is a price. The sorting that happens appears to be alphanumeric with results sorted like:

    850
    7995
    7350
    650
    etc

    I’ve already tried using orderby=meta_value_num and that produces the same results.

    I need some sort of numerical sorting that takes into account the number of digits in the number (not just sorting by the first digit) so what is the best way to go about this?

    it should be sorted like:
    7995
    7350
    850
    650
    etc

    I’ve seen some similar posts where the post array was then resorted (after the query) and I’ve seen some suggestions about using a custom sql query (not using query_posts). Any input or suggestions from the community? How have you chosen to overcome this, if you’ve faced this before? It seems to me that the DEFAULT behavior of meta_value_num is not sufficient (if you then have to do more steps to get a better sort!).

Viewing 3 replies - 1 through 3 (of 3 total)

  • If you’re going to do anything more then a relatively basic search I’d recommend using the WPDB class and writing your own SQL queries so that you have proper control over what you get back. You would have to generate the array of posts yourself, but that’s pretty easy to do.

    As an example (not tested…):

    $query = "SELECT
    p.ID
FROM ".$wpdb->posts." AS p
INNER JOIN ".$wpdb->postmeta." AS pm
    ON pm.post_id = p.ID
    AND meta_key = 'base_price'
ORDER BY CAST(pm.meta_value AS DECIMAL)";
$result = $wpdb->get_results ($query);

// I do it this way to get WP_Post objects and not just DB results
$posts = array ();

foreach ($result as $row) {
    $posts [] = get_post ($row->ID);
} // foreach ()

    There’s probably a more efficient way of doing it, but that’s what I’ve got off the top of my head for now.

    Thread Starter spiralmind

    (@spiralmind)

    Hmm. Yes I’ve seen a lot of other people have chosen this method. I’m also seeing other suggestions to use a filter hook on query_post as well. I’m experimenting tonight and tomorrow with some things.

    Thanks, catacaustic, for your input. Meow.

    Thread Starter spiralmind

    (@spiralmind)

    I guess there are no other suggestions or help from the community.

    After studying the problem in more depth, and reviewing other answers (on other sites — not this one because apparently there are no good answers from the wordpress community) it looks like the only real way to handle this problem is to write up your own custom query and avoid the hassles of using any wp related solutions. If anyone has issues with this in the future (I’m sure you will) feel free to contact me and I’ll let you know how I fixed this problem. Thanks for your input again, catacaustic.

    For those interested, the problem seems to be query_posts. It returns the data as a string and so the “built in” sorting is flawed (even if you use meta_value_num it doesn’t make a difference).

    The age old saying still applies… when you want it done right you gotta dig in and just do it/figure it out yourself.

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘query_posts: sorting by custom field numerically NOT alphanumerically’ is closed to new replies.