Esar-ul-haq Qasmi Esar-ul-haq Qasmi - 3 months ago 9
MySQL Question

Executing wrong relationship vlaue in $args in wp_Query?

i am trying to fetch data form database for my custom plugin and post type. my query arguments should be like

$args = array(
'post_type' => 'products',
'post_status'=> 'publish',
'meta_query' => array(
'relation' => 'OR',
array( 'key'=>'product_commercial',
'value'=>'on',
'compare'=>'='
),
array( 'key'=>'product_exterior',
'value'=>'on',
'compare'=>'='
)
)
);
$search_query = new WP_Query( $args );


But i am trying to add meta key values dynamically like :

$inner_arrays=array();
$count = 0;
foreach($values as $value){
if($value){

$inner_arrays[$count]['key'] .= $value;
$inner_arrays[$count]['value'] .= 'on';
$inner_arrays[$count]['compare'] .= '=';
$count++;
}
}

$args = array(
'post_type' => 'products',
'post_status'=> 'publish',
'meta_query' => array(
'relation' => 'OR',
$inner_arrays
)
);


//values are some random values (say fetched from db).

Now when i print the query using
echo "<pre>Last SQL-Query: {$search_query->request}".'<br/>';


it displays

Last SQL-Query: SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id ) INNER JOIN wp_postmeta AS mt2 ON ( wp_posts.ID = mt2.post_id ) WHERE 1=1 AND (
(
( wp_postmeta.meta_key = 'product_commercial' AND CAST(wp_postmeta.meta_value AS CHAR) = 'on' )
**AND**
( mt1.meta_key = 'product_framed' AND CAST(mt1.meta_value AS CHAR) = 'on' )
**AND**
( mt2.meta_key = 'product_horizontal' AND CAST(mt2.meta_value AS CHAR) = 'on' )
)
) AND wp_posts.post_type = 'products' AND ((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 10


PROBLEM: i am using " relation => OR ", but getting "AND" in sql query. Where i am doing wrong?

Answer

$args['meta_query'] is assigned the following:

array(
    'relation' => 'OR',
    $inner_arrays
)

If you were to inspect this, e.g. by doing print_r( $args['meta_query'] );, you'd see:

Array
(
    [relation] => OR
    [0] => Array
        (
            [0] => Array
                (
                    key => product_commercial,
                    value => on,
                    compare => =
                ), 
            [1] => Array
                (
                    key => product_exterior,
                    value => on,
                    compare => =
                )
        )
)

In other words, the $inner_arrays array has itself become a sub-array of $args['meta_query']. As documented under Custom Field Parameters:

Important Note: meta_query takes an array of meta query arguments arrays (it takes an array of arrays) - you can see this in the examples below. This construct allows you to query multiple metadatas by using the relation parameter in the first (outer) array to describe the boolean relationship between the meta queries. Accepted arguments are 'AND', 'OR'. The default is 'AND'.

Consequently you are combining your elements of $inner_arrays using the default relation of AND, whilst then combining the result of that with any other members of $args['meta_query'] (there aren't any) using the explicit relation of OR.

You must instead either append each element of $inner_arrays to meta_query, or else just set $inner_arrays['relation'] = 'OR'.