I realize this isn't the most elegant sql. I modified this query where the original query only selected from a single category (the slug field of the terms table). I am trying to obtain a result set which returns items having multiple categories (or slugs) as properties.

This does not return rows, even though the items do have both 'arizona' and 'speech-language-pathologist' in their categories list:

SELECT p.ID FROM wp_term_relationships tr 
JOIN wp_posts p ON tr.object_id = p.ID 
JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id 
JOIN wp_terms t ON tt.term_id = t.term_id 
WHERE p.post_parent = 220 AND t.slug = 'arizona' AND t.slug = 'speech-language-pathology' AND p.post_status = 'publish'

However, if I remove one of the AND conditions, this query does produce a result set:

SELECT p.ID FROM wp_term_relationships tr 
JOIN wp_posts p ON tr.object_id = p.ID 
JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id 
JOIN wp_terms t ON tt.term_id = t.term_id 
WHERE p.post_parent = 220 AND t.slug = 'arizona' AND p.post_status = 'publish'

My SQL is a little rusty, I don't see how the t.slug='something' AND t.slug='something-else' is illogical.

I realize this isn't the most elegant sql. I modified this query where the original query only selected from a single category (the slug field of the terms table). I am trying to obtain a result set which returns items having multiple categories (or slugs) as properties.

This does not return rows, even though the items do have both 'arizona' and 'speech-language-pathologist' in their categories list:

SELECT p.ID FROM wp_term_relationships tr 
JOIN wp_posts p ON tr.object_id = p.ID 
JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id 
JOIN wp_terms t ON tt.term_id = t.term_id 
WHERE p.post_parent = 220 AND t.slug = 'arizona' AND t.slug = 'speech-language-pathology' AND p.post_status = 'publish'

However, if I remove one of the AND conditions, this query does produce a result set:

SELECT p.ID FROM wp_term_relationships tr 
JOIN wp_posts p ON tr.object_id = p.ID 
JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id 
JOIN wp_terms t ON tt.term_id = t.term_id 
WHERE p.post_parent = 220 AND t.slug = 'arizona' AND p.post_status = 'publish'

My SQL is a little rusty, I don't see how the t.slug='something' AND t.slug='something-else' is illogical.

• wp-query
• query
• sql

• 1 You're asking a single value to equal two separate values. If you're interested, you could use WP_Query to retrieve the post you're looking for, ie the post that the child of 220, has status publish, and is tagged with arizona and speech-language-pathology. – MikeNGarrett Commented May 8, 2019 at 0:23
• What does the SQL query do? Is there a reason it's being done in raw SQL and not via APIs such as WP_Query? – Tom J Nowell ♦ Commented May 8, 2019 at 0:25
• 1 @TomJNowell this is the query portion of a get_col, where $wpdb->get_col( $wpdb->prepare($query, $parentID, $filterValue )); – Galloway Grumblefield Commented May 8, 2019 at 0:28
• What's the purpose of the get_col? Are you trying to get the IDs of all posts in arizona with a particular post parent? If so why not use WP_Query? It can do that, and can even be faster than a direct query in a lot of cases. I'm still unclear what problem this solves – Tom J Nowell ♦ Commented May 8, 2019 at 13:06
• @TomJNowell I'm trying to tap into a plugin, to get members of a list who have multiple categories associated with them (e.g. "arizona","speech-language-pathologist"). The get_col is what was being used by the plugin, so the result set returned from this query is used by the original plugin. At present I solved the problem (again, inelegantly) by doing two queries, one for the first category, one for the second, storing each array of post_ids in separate arrays, then finding the intersection values, thusly: $out = call_user_func_array('array_intersect',$result); – Galloway Grumblefield Commented May 8, 2019 at 13:29

1 Answer 1

You can do this using the WP APIs like this:

$args = [
    'post_type' => 'post',
    'tax_query' => [
        'relation' => 'AND',
        [
            'taxonomy' => 'your_taxonomy_name',
            'field'    => 'slug',
            'terms'    => array( 'arizona', 'speech-language-pathology' ),
        ]
    ],
];
$query = new WP_Query( $args );

Then you can use $query as you would any standard post loop.

If you only want the IDs you can use the fields option of WP_Query, but keep in mind you won't gain all the caching and performance benefits you might have gotten otherwise, especially if you're going to pass the ID into get_post

$args = [
    'post_type' => 'post',
    'tax_query' => [
        'relation' => 'AND',
        [
            'taxonomy' => 'your_taxonomy_name',
            'field'    => 'slug',
            'terms'    => array( 'arizona', 'speech-language-pathology' ),
        ]
    ],
    'fields' => 'ids'
];
$query = new WP_Query( $args );
$posts = $query->get_posts(); // $posts is an array of post IDs