I’d like to query published posts for a specific category, and retrieve a custom taxonomy at the same time.
From my understanding of the relational structure of the DB, I fear this isn’t possible with a single query, or some serious looping to clean up the duplicate results.
The following query works. It selects published posts in the category ‘Peru’. What’s missing is pulling the name of the city, which is stored under the custom taxonomy ‘location’.
Like I said, I believe this will either require two queries or cleaning up the duplicate records (as each post will return a row for the category and a row for the location).
Likewise, there will also be a need to query for both a specific location (‘Lima’) and category (‘Peru’). Ugh.
$query = $wpdb->prepare( "SELECT $wpdb->posts.ID, $wpdb->posts.post_title, $wpdb->posts.post_date, $wpdb->posts.comment_count FROM $wpdb->posts LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id) INNER JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id) INNER JOIN $wpdb->terms ON($wpdb->terms.term_id = $wpdb->term_taxonomy.term_id) WHERE ($wpdb->posts.post_type = 'post' AND $wpdb->posts.post_status = 'publish' AND $wpdb->posts.post_parent=0 AND $wpdb->term_taxonomy.taxonomy = 'category' AND $wpdb->terms.name = 'Peru') ORDER BY $wpdb->posts.post_date DESC;"); $resultsArray = $wpdb->get_results( $query, ARRAY_A );
- The topic ‘Custom Select Query: Both Category & Taxonomy?’ is closed to new replies.