need help with a query of a meta key with prices with commas and$ (4 posts)

  1. munkeyhouse
    Posted 3 years ago #

    i need help with a query for a meta key 'price' the problem is i need it to display 15 posts per page, and the real pain is the prices are entered into the database with $'s and commas, ie. $5,000. we want to be able to perform a query for price ranges and have them display asc ie. an "under 50$" query would display all posts with a meta key price under $50... there are also prices entered as "$TBD" "$ Upon Request", we need to exclude those from the results.

    thanks in advance

  2. bcworkz
    Posted 3 years ago #

    Have you tried using CAST() in your query to convert the value to decimal type? Not sure it'll work correctly, but worth a try. If you're using WP_Query, it probably doesn't know how to parse that as an argument, you'll need to add it to the query using one of it's filters. Or use $wpdb instead.

  3. munkeyhouse
    Posted 3 years ago #

    i have a sql query now, that will do basically that, the real problem is it ignores pagination, so i get the sort i want, but it displays every post on one page...

    $querystr = "
    	SELECT wposts.*, ABS(
    REPLACE(SUBSTRING(wpostmeta.meta_value, 2)
    , ',', '')) AS sortby
    	FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
    	WHERE wposts.ID = wpostmeta.post_id
    	AND wpostmeta.meta_key = 'price'
            AND wpostmeta.meta_value != '$TBA'
            AND wpostmeta.meta_value != 'REQUEST $'
            AND ABS(REPLACE(REPLACE(wpostmeta.meta_value, '$', ''), ',', '')) > '50'
            AND ABS(REPLACE(REPLACE(wpostmeta.meta_value, '$', ''), ',', '')) < '150'
    		AND post_status='publish'
            ORDER BY sortby asc
    $pageposts = $wpdb->get_results($querystr, OBJECT);
    if ($pageposts) : foreach ($pageposts as $post): setup_postdata($post);
    	$my_meta = get_post_meta(get_the_ID(), '_my_meta', TRUE);
        <?php get_template_part( 'grid', get_post_format() ); ?>
        <?php endforeach; endif; ?>
  4. bcworkz
    Posted 3 years ago #

    WP_Query has a bunch of filters that allow you to directly modify it's current SQL query in order to work around it's parsing limitations. Now that you have a working SQL, you just need to figure out how to modify the query in WP_Query so that it ends up looking the same as yours. Then you can make use of it's pagination.

Topic Closed

This topic has been closed to new replies.

About this Topic


No tags yet.