Using OR conditions in meta_query for query_posts argument

1 Views

I have searched high and low for this but I cant seem to locate anything.

I am trying to filter some results and when I make an args array like this and pass it to query_posts I noticed that the generated SQL uses AND instead of OR for the ‘class_1_days’ value. I would like to make than an OR. How would I go about doing this? (I am using LIKE there as the data is serialized, otherwise I would have tried an “IN” and pass an array. What is the proper way to go about this?

Read More
$args = array(
            'taxonomy' => 'courses', 
            'term' => 'drawing', 
            'post_type' => 'school', 
            'paged' => $paged,
            'meta_query' =>
                #$checkbox_array,
                array(
                    array(
                        'key' => 'instructor',
                        'value' => '1128',
                        'compare' => 'LIKE',
                    ),
                    array(
                        'key' => 'class_1_days',
                        'value' => 'Friday',
                        'compare' => 'LIKE',
                    ),
                    array(
                        'key' => 'class_1_days',
                        'value' => 'Saturday',
                        'compare' => 'LIKE',
                    ),
                )

        );

This is the relevant SQL that is being generated where I noticed the AND 

[138] => Array
    (
        [0] => SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  INNER JOIN     wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) 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_term_relationships.term_taxonomy_id IN (18) ) AND wp_posts.post_type = 'school' AND     (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') AND (     (wp_postmeta.meta_key = 'instructor' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '%1128%')
AND  (mt1.meta_key = 'class_1_days' AND CAST(mt1.meta_value AS CHAR) LIKE '%Friday%')
AND  (mt2.meta_key = 'class_1_days' AND CAST(mt2.meta_value AS CHAR) LIKE '%Saturday%') ) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 5
Post Views: 1

Related posts

2 comments

  1. Use 'relation' => 'OR' as in the Codex example below:

    $args = array(
    'post_type' => 'product',
    'meta_query' => array(
        'relation' => 'OR', /* <-- here */
        array(
            'key' => 'color',
            'value' => 'blue',
            'compare' => 'NOT LIKE'
        ),
        array(
            'key' => 'price',
            'value' => array( 20, 100 ),
            'type' => 'numeric',
            'compare' => 'BETWEEN'
        )
    )
);
$query = new WP_Query( $args );

    That will give you an OR across all of your meta_query blocks. If what you need is something like WHERE "instructor" = 1128 AND (class_1_days = "value1" OR class_1_days = "value2") then something like this would do it if your data wasn’t serialized.

    $args = array(
        'taxonomy' => 'courses', 
        'term' => 'drawing', 
        'post_type' => 'school', 
        'paged' => $paged,
        'meta_query' =>
            array(
                array(
                    'key' => 'instructor',
                    'value' => '1128',
                    'compare' => 'LIKE',
                ),
                array(
                    'key' => 'class_1_days',
                    'value' => array('Friday','Saturday')
                    'compare' => 'IN',
                )
            )

    );

    Given that you have serialized data this is going to be difficult. You could write a filter for posts_where but I’d expect the result to inefficient and prone to error.

    The correct approach for cases like this where you need to search/filter/sort by some piece of information is, in my experience, to not store your data as a serialized array in the first place. That is simply the wrong approach if you need queries like this.

    If I were taking over this project I would loop through the data that needs to be queried in this way and re-save each piece at a key/value pair. The parts you are not querying can stay serialized. There is no (good) way to do this in MySQL with MySQL functions either as part of the query you are attempting or as some stand-alone query. I have seen attempts at creating a MySQL “unserialize” function but I don’t see the point. Re-saving the data is the way to go.

    It should be a fairly simple query to get all of the class_1_days data. Then loop over it and save the parts you need to query as independent keys and put the rest back as serialized data.

  2. According to the WordPress codex nested arrays can be used to construct complex queries. In your example your need something like this:

    $args = array(
    'taxonomy' => 'courses', 
    'term' => 'drawing', 
    'post_type' => 'school', 
    'paged' => $paged,
    'meta_query' =>
        array(
           'relation' => 'AND', // default relation
            array(
                'key' => 'instructor',
                'value' => '1128',
                'compare' => 'LIKE',
            ),
            array(
                'relation' => 'OR',
                array(
                    'key' => 'class_1_days',
                    'value' => 'Friday',
                    'compare' => 'LIKE',
                ),
                array(
                    'key' => 'class_1_days',
                    'value' => 'Saturday',
                    'compare' => 'LIKE',
                ),
            )
        )
);

Comments are closed.