Filter query based on min and max price for custom post type

I have an ACF field called price. price is assigned to the vehicle post type.

I also have a filter which allows users to show vehicle's based on defined min price and max price.

I'm trying to showcase the results of the vehicle's which fall between the defined min price and max price range.

Here's what I have so far:

<?php

// get data from url
$min_price = $_GET['min-price'];
$max_price = $_GET['max-price'];

global $post;

$args = array(
  'post_type' => 'vehicle',
  'post_status' => 'publish',
  'orderby' => 'publish_date',
  'order' => 'DESC',
  'meta_query' => array(
    array(
      'key'     => 'price',
      'value' => array($min_price, $max_price),
      'compare' => 'BETWEEN',
      'type'    => 'numeric',
    )
  ),
);


$query = new WP_Query( $args );

if($query->have_posts() ) :
  while ( $query->have_posts() ) : $query->the_post();
    $price = get_field("price");
    $price = preg_replace("/[^0-9]/", "", $price);
      
    echo the_title();
  endwhile; wp_reset_postdata(); 
  
else : ?>
  <h2><?php _e("no posts found"); ?></h2>
<?php endif; ?>


?>

A var_dump($price) returns string(0) "". I obviously need $price to be defined in the while loop, but unsure on how I would query it above in that case? I only want to return the posts which fall in that range.

Solution

Because you are storing numeric data as a string, you are going to run into problems in a bunch of places over time. Even simple things like sorting now become difficult. (And I'm not judging, it is what it is.) If you can get this data stored as true numeric and deal with formatting elsewhere, your code will be easier to maintain in the long run.

The native WordPress query will unfortunately not be able to get you where you are going because of the above limitation. However, WordPress has tons of hooks and there's one in particular, post_clauses that allows you to modify the WHERE clause of the SQL query to fit your needs. (There's actually a bunch, I just usually prefer to do it in this specific one.)

First, I'm going to tweak your $args by adding one additional parameter. The name doesn't matter as long as it doesn't conflict with anything else, and I'm using FIX_RANGE_QUERY.

$args =
    [
        'FIX_RANGE_QUERY' => true,
        'post_type' => 'vehicle',
        'post_status' => 'publish',
        'orderby' => 'publish_date',
        'order' => 'DESC',
        'meta_query' => [
            [
                'key' => 'price',
                'value' => [$min_price, $max_price],
                'compare' => 'BETWEEN',
                'type' => 'numeric',
            ]
        ],
    ];

$query = new WP_Query($args);

This alone won't change anything, you also need a filter which should probably be before the above, ideally in functions.php or similar. I've added a bunch of comments to this code to hopefully walk through everything it does, but the gist is that it modifies the WHERE clauses of the SQL query to include a regex to remove non-digits before doing the cast and comparison.

add_filter(
    'posts_clauses',
    static function ($clauses, WP_Query $query) {
        // Look for our magic variable so that we only modify our certain query
        if (true === ($query->query['FIX_RANGE_QUERY'] ?? false)) {

            // Make sure that we've got something in the WHERE position
            $where = $clauses['where'] ?? null;
            if ($where) {

                // We need this in order to get the prefix so that we are safe if this code is migrated
                global $wpdb;

                // The CAST() stuff is WordPress because the query specifies a NUMERIC type
                $searchString = "CAST({$wpdb->prefix}postmeta.meta_value AS SIGNED)";

                // Replace the above with an inner regex that removes everything except digits
                $replaceString = "CAST(REGEXP_REPLACE({$wpdb->prefix}postmeta.meta_value, '[^0-9]+', '') AS SIGNED)";

                // Reset the main clause
                $clauses['where'] = str_replace($searchString, $replaceString, $where);
            }
        }

        // No matter what, we must return this, otherwise EVERY query will break.
        return $clauses;
    },
    9999,
    2
);

The above code changes the WordPress query from this:

 AND ( 
  ( wp_postmeta.meta_key = 'price' AND CAST(wp_postmeta.meta_value AS SIGNED) BETWEEN '5' AND '100' )
) AND wp_posts.post_type = 'vehicle' AND ((wp_posts.post_status = 'publish'))

To this:

 AND ( 
  ( wp_postmeta.meta_key = 'price' AND CAST(REGEXP_REPLACE(wp_postmeta.meta_value, '[^0-9]+', '') AS SIGNED) BETWEEN '5' AND '100' )
) AND wp_posts.post_type = 'vehicle' AND ((wp_posts.post_status = 'publish'))

This code is marginally fragile, just FYI. If any spacing is off the str_replace won't find what it is looking for. But overall it should be pretty consistent still.