Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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?

$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
share|improve this question

1 Answer 1

up vote 0 down vote accepted

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 "instructor" = 1128 AND (class_1_days = "value1" OR class_1_days = "valu21") 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.

share|improve this answer
    
Thank you so much for your reply. Is there a way I can do an AND or? I am trying to Keep the instructor bit. Your example makes them all OR. I just need the class_1_days bit to be an or. Similar to if I had it as 'value' => array(bla,bla,bla), 'compare' => IN Basically where Instructor = 'bla' AND value IN (bla,bla,bla) –  Devario Johnson Jun 24 '13 at 16:06
    
Thanks @S_ha_dam. Is there a way I can unserialze the DB. This is something I took on second hand so I dont know what all that will effect. I saw a link to an app that did just that, however the link to it was broken. –  Devario Johnson Jun 24 '13 at 16:09
    
Thanks, however simply resaving it wont work this is a live environment where this data is constantly being saved and the class_1_data is only part of the equation. There are multiple ones class_0_data, class_2_data, etc and so on and so forth, and those are added by the users. I think the best approach may be to backtrack and find a way to reinstall the module and make it so that it doesnt save serialized (if i figure out how) and go from there X-( ... I didn't mention the other classes portion cuz I figured I could figure it out on my own –  Devario Johnson Jun 24 '13 at 16:23
    
You certainly will have to refactor the "save" function but will need to resave the existing data as well or you will end up with a weird mix of serialized and not-serialized. Get the code working on a dev server then put the live one in maintenance mode for twenty minutes at some low-traffic time of day. –  s_ha_dum Jun 24 '13 at 16:27
    
Oh yes certainly I will have to change the current data! this blows. I thought this was going to be an easy fix ugh! –  Devario Johnson Jun 24 '13 at 16:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.