phpsqlwordpress

WordPress - Optimize the Meta Query for 3 meta keys at a time

Records in post_meta as follow:

post_id key value
1 price 10
1 price_to 5000
2 price_type 'POA'
3 price 12000
3 price_to 17000
4 price 1200
4 price_to 8000
5 price_type 'POA'

Code Snippet

<?php
$price_from = '20';
$price_to = '700';

$meta_query_price[] = array(
    'relation' => 'OR',
    array(
        'relation' => 'AND',
        array(
            'key' => 'price',
            'value'    =>  $price_from,
            'type'     => 'NUMERIC',
            'compare' => '<='
        ),
        array(
            'key' => 'price_to',
            'value'    =>  $price_to,
            'type'     => 'NUMERIC',
            'compare'  => '>='
        ),
    ),
    array(
        'key' => 'price_type',
        'value'    =>  'POA',
        'type'    =>  'value',
        'compare'  => 'LIKE'
    ),
);

Output

post_id key value
1 price 10
1 price_to 5000
2 price_type 'POA'
5 price_type 'POA'

Above code is executed perfectly issue is when I add price_type to the meta_query to extend data it will cause the speed issue. The result come perfect but time will be very high.

So, is there any tricks to do this kind of query in WordPress?

Solution

  • Your placement of meta_query was wrong. That's why the you not able to get proper output.

    Try below snippet

    <?php
$meta_query_price[] = array(
    'relation' => 'OR',
    array(
        'key' => 'price_type',
        'value'    =>  'POA',
        'type'    =>  'value',
        'compare'  => 'LIKE'
    ),
    array(
        'relation' => 'AND',
        array(
            'key' => 'price',
            'value'    =>  $price_from,
            'type'     => 'NUMERIC',
            'compare' => '<='
        ),
        array(
            'key' => 'price_to',
            'value'    =>  $price_to,
            'type'     => 'NUMERIC',
            'compare'  => '>='
        ),
    ),
);

    Condition 1: Price Type Check:

    Condition 2: Price Range Check: