1

That title was a real mouthful.

I have a custom post type called work with three custom taxonomies called work-client, work-type, and work-service. I'm using Advanced Custom Fields to make a nice clean backend for them, creating a "choose one" field called work_client for the work-client, and a "choose multiple" field for work-service and work-type called work_services and work_type which saves the taxonomy IDs as a serialized array.

TLDR: Posts have a work_client meta field with a single taxonomy ID, and also work_services and work_type meta fields containing serialized arrays of multiple taxonomy IDs.

What I'm trying to do is create a robust post filtering system where you have a checkbox list of all the Clients, Types and Services, and can choose one or multiple of each to filter by. The different types of filters have an AND relationship, but different values within one filter have an OR relationship. So for example:

If you choose Clients A and B, Types X and Y, and Service 1, you should get back posts only post that are from only Client A or B, AND only Type X or Y, AND only Service 1. I hope that makes sense.

I have learned that it's impossible to use both "AND" and "OR" relationships in a WP_Query, so I've resorted to making a $wpdb->get_results() call to accomplish this. Based on some examples of similar (but not quite the same) attempts, this is what I've come up with:

    SELECT $wpdb->posts.* FROM $wpdb->posts
    INNER JOIN $wpdb->postmeta ON ($wpdb->posts.ID = $wpdb->postmeta.post_id)
    WHERE 1=1
    AND $wpdb->posts.post_type = 'work'
    AND ($wpdb->posts.post_status = 'publish' OR $wpdb->posts.post_status = 'private')
    AND (
        (
            obobq8u_postmeta.meta_key = 'work_client' 
            AND (
                CAST(obobq8u_postmeta.meta_value AS CHAR) IN ('149')
            )
        ) OR (
            obobq8u_postmeta.meta_key = 'work_client' 
            AND (
                CAST(obobq8u_postmeta.meta_value AS CHAR) IN ('231')
            )
        )
    ) 
    AND (
        (
            obobq8u_postmeta.meta_key = 'work_services' 
            AND (
                CAST(obobq8u_postmeta.meta_value AS CHAR) LIKE ('%247%')
            )
        )
    ) 
    AND (
        (
            obobq8u_postmeta.meta_key = 'work_type' 
            AND (
                CAST(obobq8u_postmeta.meta_value AS CHAR) LIKE ('%244%')
            )
        )
    ) 
    GROUP BY $wpdb->posts.ID ORDER BY $wpdb->posts.post_title ASC

I know there are posts in the system by both client 149 and 231 that have Service 247 and Type 244, but running that query comes back with no results.

However, if I run it with only ONE of the meta queries, that part of it works fine. So for example this query gives me all the posts that have either client 149 or 231

    SELECT $wpdb->posts.* FROM $wpdb->posts
    INNER JOIN $wpdb->postmeta ON ($wpdb->posts.ID = $wpdb->postmeta.post_id)
    WHERE 1=1
    AND $wpdb->posts.post_type = 'work'
    AND ($wpdb->posts.post_status = 'publish' OR $wpdb->posts.post_status = 'private')
    AND (
        (
            obobq8u_postmeta.meta_key = 'work_client' 
            AND (
                CAST(obobq8u_postmeta.meta_value AS CHAR) IN ('149')
            )
        ) OR (
            obobq8u_postmeta.meta_key = 'work_client' 
            AND (
                CAST(obobq8u_postmeta.meta_value AS CHAR) IN ('231')
            )
        )
    ) 
    GROUP BY $wpdb->posts.ID ORDER BY $wpdb->posts.post_title ASC

And this query gives me results that are only service 247:

    SELECT $wpdb->posts.* FROM $wpdb->posts
    INNER JOIN $wpdb->postmeta ON ($wpdb->posts.ID = $wpdb->postmeta.post_id)
    WHERE 1=1
    AND $wpdb->posts.post_type = 'work'
    AND ($wpdb->posts.post_status = 'publish' OR $wpdb->posts.post_status = 'private')
    AND (
        (
            obobq8u_postmeta.meta_key = 'work_services' 
            AND (
                CAST(obobq8u_postmeta.meta_value AS CHAR) LIKE ('%247%')
            )
        )
    ) 
    GROUP BY $wpdb->posts.ID ORDER BY $wpdb->posts.post_title ASC

The issue seems to be combining the two together. It's likely that I'm doing the "join" wrong, but I'm having trouble finding help for this specific issue and I'm definitely not a SQL query expert.

What is it that I'm doing wrong? Hopefully I'm close, at least?

  • From what I understand, support for multiple relations will be included in version 4.1 which should make this a lot easier. core.trac.wordpress.org/ticket/29642 – iyrin Dec 5 '14 at 22:20
  • Wow that's exactly what I need. So basically if I can put this off a week it'll be a piece of cake? That's nuts. – Ryan Giglio Dec 5 '14 at 22:25
  • Looks like it. This seems like a really cool feature plugin devs. – iyrin Dec 5 '14 at 22:32
0

Fortunately, this complicated database query is no longer needed. WordPress 4.1 added the ability to make complex nested meta queries which is exactly the thing I was trying to accomplish.

Here is the Trac ticket with the feature: https://core.trac.wordpress.org/ticket/29642

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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