0

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.

  • 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 May 8 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 May 8 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 May 8 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 May 8 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 May 8 at 13:29
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
  • 1
    Thanks. My rep is too low to up-vote, but I did accept as answer. I am looking for a query solution rather than post-processing arrays, so the help is appreciated. – Galloway Grumblefield May 8 at 15:17
  • @GallowayGrumblefield you don't need to juggle arrays, you can use wp_list_pluck to grab a single field from an array of objects, or just use the fields parameter in WP_Query to only fetch the ids. I've updated my answer with an example – Tom J Nowell May 8 at 15:35

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.