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?
