WordPress.org

Ready to get started?Download WordPress

Codex

Making Advanced Taxonomy Queries

This page is marked as incomplete. You can help Codex by expanding it.

Contents

Description

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.

Prerequisites & Assumptions

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.

Built-in WordPress Helper Functions

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...

Examples

Now let's take a look at a few advanced taxonomy-based queries and use-cases...

SELECT Taxonomy Terms by Post Meta

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: 'event-date' and '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.

Notes: '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.

Related

Articles

Code Documentation

  • Class: WP_Query - Detailed Overview of class WP_Query
  • Object: $wpdb - Overview on the use of the $wpdb object
  • Function: get_query_var()
  • Function: query_posts() - Create additional custom query
  • Function: get_post() - Take an ID of an item and return the records in the database for that article
  • Function: get_posts() - A specialized function that returns an array of items
  • Function: get_pages() - A specialized function that returns an array of pages
  • Function: have posts() - a condition that determines whether the query returned an article
  • Function: the_post() - Used to automatically set the loop after a query
  • Function: rewind_posts() - Clears the current loop
  • Function: setup_postdata() - Sets the data for a single query result within a loop
  • Function: wp_reset_postdata() - Restores the previous query (usually after a loop within another loop)
  • Function: wp_reset_query()
  • Function: is_main_query() - Ensures that the query that is being changed is only the main query
  • Action Hook: pre_get_posts - Change WordPress queries before they are executed
  • Action Hook: the_post - Modify the post object after query
  • Filter Hook: found_posts - Changes the value of the object found_posts WP_Query