Database model & Taxonomies (WordPress, 2024)
Naar navigatie springen
Naar zoeken springen
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
Case: See product information including values from a specufic attribute taxonomy (2024.07)
→ Database model - Products & attribute taxonomies