Posts just won't order by custom field

  • I’ve hunted around for an answer to this for two days now, and just can’t seem to get this code to work. I’m trying to order results in ascending order by a custom field called ‘price’. I understand there’s an issue ordering by numbers rather than strings, but I have tried every combination of meta_value, meta_value_num, with and without quotes, and am getting absolutely nothing. The thing I really don’t understand is I only get results of any description when I set the ‘greater than >’ to be ‘less than <‘ zero, which is palpable nonsense. The results I get then bring back an unordered list of the posts I’m after.

    I can’t give a link to the site because it’s running off WAMP, but hopefully some merciful soul will see something in the code:

    global $wpdb;
	$querystr = "
	SELECT *
	FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta , $wpdb->postmeta wpostmeta2, $wpdb->postmeta wpostmeta3
	WHERE wposts.ID = wpostmeta.post_id AND wposts.ID = wpostmeta2.post_id AND wposts.ID = wpostmeta3.post_id
	AND wpostmeta.meta_key = 'closed'
	AND wpostmeta.meta_value = '0'
	AND wpostmeta2.meta_key = 'pay_online'
	AND wpostmeta2.meta_value = '1'
	AND wpostmeta3.meta_key = 'price'
	AND wpostmeta3.meta_value_num > '0'
	AND wposts.post_status = 'publish'
	AND wposts.post_type = 'auction'
	ORDER BY wpostmeta3.meta_value_num ASC";
	$pageposts = $wpdb->get_results($querystr, OBJECT);
	if ($pageposts): global $post;
             	foreach ($pageposts as $post):
			setup_postdata($post);
			widget_market();
		endforeach;
	else : ?> <div class="padd100"><p class="center"><?php _e("Be the first to list here"); ?>.</div>

    [No bumping. If it’s that urgent, consider hiring someone or or asking on the #wordpress IRC Channel.]

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

  • How are your price values formatted? If they have a leading currency sign, then they will not sort as numbers. In order to sort correctly, you should enter the price with no currency symbol or commas. Do all your formatting just before displaying the value.

    Thread Starter hermitjim

    (@hermitjim)

    Thanks, vtxyzzy, good thought but not applicable here as the price is just a number. Wondering whether I should explicitly cast it as a floating point before it hits the database? Grasping at straws though…

    Thanks again for your input

    I suspect that MySQL doesn’t know what to do with meta_value_num:

    AND wpostmeta3.meta_value_num > '0'

    and

    ORDER BY wpostmeta3.meta_value_num ASC";

    Try using this:

    AND (wpostmeta3.meta_value + 0) > '0'
    ORDER BY (wpostmeta3.meta_value + 0) ASC";
    Thread Starter hermitjim

    (@hermitjim)

    Doesn’t return any results at all. Just had a look in the database at the ‘price’ field in wp_postmeta; price meta_value type is ‘long text’ – should it be numeric? Or is that just a red herring?

    Adding a zero to the value should force it to cast as numeric.

    Perhaps I made a mistake in my coding. Can you try issuing the query in phpMyAdmin or a similar tool?

    Or try taking out the clauses that depend on wpostmeta3.meta_value to see if you get any results.

    Thread Starter hermitjim

    (@hermitjim)

    OK, so if I remove all refs to wpostmeta3.meta_value including orderby, I get an ascending alpha list on post title

    Thread Starter hermitjim

    (@hermitjim)

    Not sure how to frame that as an SQL query in PHPMyAdmin – I can see the SQL query box in PHPMyAdmin, but not quite clear how to reconstruct the query from the code (sorry)

    Just replace all $wpdb-> by the table prefix used in your database (usually just wp_).

    If you add the order by clause back in, do you get any results? In what order?

    I blew it. The ‘AND’ clause with the + 0 should be like this:

    AND (wpostmeta3.meta_value + 0) > 0

    No quotes around the zero.

    Thread Starter hermitjim

    (@hermitjim)

    Still not playing ?? Haven’t had a chance to SQL it yet (real life intervened) will try later today.

    Many thanks

Viewing 10 replies - 1 through 10 (of 10 total)
  • The topic ‘Posts just won't order by custom field’ is closed to new replies.