Getting posts by custom field value

Question

I need to get posts with a price less than or equal to for example '1000'. The price is set in an arbitrary custom field. Trying to posts a price that is less than or equal to 1000

$posts = new WP_Query( {
  'post_type'  => 'post',
  'meta_query' => array(
    'key'     => 'price',
    'compare' => '<=',
    'value'   => 1000
  ),
} );

Expect to get posts with price 1000, 999, 998, etc.

The problem is that many posts have already been created, in which the price is set in such a way that the word for example "from 1000" is added at the beginning. And some are simply given the number 1000. 'value' is compared to the number in which there is a string and the result is not correct.

Is there any way to get the posts right? Or do I need to remove lines from custom fields price?

Answer 1

first, you have to loop through all posts that have this meta key (your custom field) and then update post meta with replacing the word (from) to leave the number alone in the meta value.

//paste the following code in your plugin or theme functions.php
add_action( 'init', function () {
    if ( ! get_option( 'prefix_correct_data' ) ) {
        $metakey = 'YOUR_META_KEY';
        $loop    = new WP_Query( [
            'meta_query' => array(
                array(
                    'key' => $metakey,
                ),
            )
        ] );
        if ( $loop->have_posts() ) {
            while ( $loop->have_posts() ) {
                $loop->the_post();
                $old_meta = get_post_meta( get_the_ID(), $metakey, true );
                update_post_meta( get_the_ID(), $metakey, str_replace( 'from', '', $old_meta ) );
            }
        }
        update_option( 'prefix_correct_data', 1 );
        wp_reset_postdata();
    }
} );

this code cleans the meta value from the word (from). just change the meta key variable with yours

then you can run your wp_query

$posts = new WP_Query( [
    'post_type'  => 'post',
    'meta_query' => array(
        'key'     => 'price',
        'compare' => '<=',
        'value'   => 1000
    ),
] );

make sure to write the word (from) as you will find in the saved custom fields.

-- the first function will run one time only and you will have to change the option key to run it again --

Answer 2

You would use WP_Meta_Query to query the value and type :

value (string|array) - Custom field value. It can be an array only when compare is 'IN', 'NOT IN', 'BETWEEN', or 'NOT BETWEEN'. You don't have to specify a value when using the 'EXISTS' or 'NOT EXISTS' comparisons in WordPress 3.9 and up.

type (string) - Custom field type. Possible values are 'NUMERIC', 'BINARY', 'CHAR', 'DATE', 'DATETIME', 'DECIMAL', 'SIGNED', 'TIME', 'UNSIGNED'. Default value is 'CHAR'.

$args = array(
    'relation' => 'OR', // Optional, defaults to "AND"
    array(
        'key'     => '_key',
        'value'   => 'Value',
        'compare' => '='
    )
);
$meta_query = new WP_Meta_Query( $args );

Answer 3

I'm afraid this need to be solved at DB level.

In case you have a list of wrong prefixes such as "from ", "sale " etc, you should update the postmeta table with something along the lines of:

UPDATE wp_postmeta SET meta_value = TRIM(REPLACE('from ', '', meta_value)) WHERE meta_key = 'price';

For each prefix you should execute the above line.