Codex tools: Log in
This article covers the topic of performing advanced taxonomy-centric queries (such as categories, tags, or custom taxonomies).
Making advanced post queries in WordPress is very well documented, but plugin and theme developers may find that they need to leverage WordPress's taxonomy system with the same level of flexibility as the post system.
This article assumes that you are already very familiar with making MySQL queries both in and out of WordPress. You should also be very familiar with WordPress's $wpdb class object and it's various methods.
This article is about making very advanced queries. Keep in mind that WordPress contains a large number of helper functions for making simple taxonomy and term queries. Please see if these functions meet your needs before continuing on with this article...
Now let's take a look at a few advanced taxonomy-based queries and use-cases...
The following query demonstrates one way to fetch terms based on the post meta of posts that they are attached to.
For example, let's say that you've built a simple "event" system in WordPress, for which you have one custom post type (
'event' ), one custom taxonomy (
'locations' ), and two pieces of important post meta:
'members-only'. Now let's assume that you want to get a list of all the locations that have upcoming/future events, provided those events aren't restricted to members only.
This is a complex scenario as it involves joining WordPress's taxonomy and term tables with both the post and post meta tables. This is not possible using WordPress's simplified query mechanisms (which have been constructed to be post-centric, not term-centric). The following example shows one solution, complete with SQL-friendly comments explaining the step-by-step logic.
'event-date' is a numeric value in the WordPress database generated by the php time() function.
'members-only' is a simple bit value that is set when used and deleted if unused (so its mere presence indicates a true condition).
$locations_with_upcoming_events = $wpdb->get_results(" #Select only the term info SELECT DISTINCT t.* #Start by selecting terms table... FROM wp_terms AS t #Join the taxonomy table to terms... INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id #Join relationship table to term taxonomy JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id #Join posts to the term relationship table JOIN wp_posts AS p ON p.ID = tr.object_id #Join first instance of meta table (we need one instance per meta key) JOIN wp_postmeta AS pm1 ON (p.ID = pm1.post_id AND pm1.meta_key = 'event-date') #Join second instance of meta table (since we are checking against TWO meta keys) JOIN wp_postmeta AS pm2 ON (p.ID = pm2.post_id AND pm2.meta_key = 'members-only') #Limit terms to a specific taxonomy (in this case, our custom 'locations' taxonomy) WHERE tt.taxonomy IN ('locations') #Limit terms to a specific post type (in this case, our custom 'event' post type) AND p.post_type = 'event' #We only want terms that are attached to events that haven't happened yet AND ( pm1.meta_key = 'event-date' AND CAST(pm1.meta_value AS SIGNED) >= '".time()."') #Exclude anything with the 'members-only' key (deny access) AND ( pm2.post_id IS NULL ) #Order by term name, ascending... ORDER BY t.name ASC;");
Important: Also note that
$wpdb prefix and/or table properties have been omitted for readability. Your queries should always use
$wpdb properties in place of hard-coded wp_ prefixes or table names to ensure maximum compatibility.
The gist here is that you simply need to join the meta table once for EACH piece of meta you want to query against.