Database model & Taxonomies (WordPress, 2024)

Case: Find all products, associated with a given attribute taxon (2024.03)

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:

wp_terms *-* wp_term_taxonomy *-* wp_term_relationships *-* wp_posts

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.

Solution

In SQL:

select
   wp_posts.ID,
   wp_posts.post_title,
   wp_posts.post_excerpt,
   wp_posts.post_type,
   wp_term_taxonomy.taxonomy,
   wp_terms.term_id,
   wp_terms.slug
from
   wp_posts
join
   wp_term_relationships
   on
   wp_posts.ID = wp_term_relationships.object_id
join
   wp_term_taxonomy
   on
   wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
join
   wp_terms
   on
   wp_term_taxonomy.term_id = wp_terms.term_id
where
   wp_terms.term_id = 44564
   and
   wp_posts.post_type = 'product';

Case: See prices of all products, associated with a certain taxon (2024.03)

This works really good. Unfortunately, you only see the prices and not the name of the products, etc.:

term_id=44564

sql="select wp_posts.ID from wp_posts "
sql+="join wp_term_relationships on wp_posts.ID = wp_term_relationships.object_id "
sql+="join wp_term_taxonomy on "
sql+="wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id "
sql+="join wp_terms on wp_term_taxonomy.term_id = wp_terms.term_id "
sql+="where wp_terms.term_id = ${term_id} "
sql+="and wp_posts.post_type = 'product' "

product_ids=$(wp db query "$sql" --skip-column-names --silent)

echo "$product_ids" | parallel wp post meta get {} _price

See also

• Taxonomieën - Datamodel (WordPress, 2022)