Database model & Taxonomies (WordPress, 2024)

Case: Find all products, associated with a attribute taxon

Problem

How to select all products that are associated with attribute taxonomy pa_device taxon forklift-trucks, which has term_id=44564 (table wp_terms)

Additional information

The link is more complicated than just wp_terms *-* some lookup-table *-* wp_posts, as it involves two lookup tables. In WordPress, the link between terms (such as those in the "pa_device" attribute taxonomy) and posts (including products) is established through the database tables wp_term_taxonomy, wp_term_relationships, and wp_posts.

Here's how it works:

• wp_term_taxonomy: This table stores information about taxonomies and their terms. Each term is associated with a specific taxonomy via the taxonomy column. The term ID (term_id) and the associated taxonomy ID (term_taxonomy_id) are stored in this table.
• wp_term_relationships: This table establishes relationships between terms and posts. It maps the object_id (post ID) to the term_taxonomy_id (the taxonomy ID). Each row in this table represents a relationship between a term and a post. For example, if a product is associated with a term in the "pa_device" attribute taxonomy, there will be a row in this table linking the product's post ID to the taxonomy ID of the term.
• wp_posts: This table stores information about posts, including products in the case of WooCommerce. Each post has a unique ID, and it can be of different types (post types), such as "product", "post", "page", etc.

So, to find all products associated with a specific term (identified by its term ID), you would:

• Look up the term_taxonomy_id for that term in the wp_term_taxonomy table
• Then, search the wp_term_relationships table for rows where the term_taxonomy_id matches the desired term
• Retrieve the object_id (post ID) from these rows
• Finally, query the wp_posts table to retrieve the product details using the object_id.

See also

• Taxonomieën - Datamodel (WordPress, 2022)