Tổng hợp

SQL get all woocommerce products baseon a product tag.

Đã đăng trên 11/11/202511/11/2025 bởi Thành Car

To retrieve all WooCommerce products associated with a specific product tag using SQL, you need to join several WordPress database tables. The primary tables involved are wp_posts (for product information), wp_term_relationships (to link posts to terms), wp_term_taxonomy (to define the taxonomy of the terms), and wp_terms (to store the tag names).

Here is an SQL query that accomplishes this, assuming your WordPress table prefix is wp_:

Code

SELECT p.ID AS product_id, p.post_title AS product_name, p.post_status AS product_status FROM wp_posts AS p INNER JOIN wp_term_relationships AS tr ON p.ID = tr.object_id INNER JOIN wp_term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id INNER JOIN wp_terms AS t ON tt.term_id = t.term_id WHERE p.post_type = 'product' AND p.post_status = 'publish' -- Or 'any' to include drafts, pending, etc. AND tt.taxonomy = 'product_tag' AND t.slug = 'your-product-tag-slug'; -- Replace with the actual slug of your product tag

Explanation:

SELECT p.ID AS product_id, p.post_title AS product_name, p.post_status AS product_status: This selects the product ID, title, and status from the wp_posts table.
FROM wp_posts AS p: Specifies the main table for products.
INNER JOIN wp_term_relationships AS tr ON p.ID = tr.object_id: Joins wp_posts with wp_term_relationships to link products to terms. object_id in wp_term_relationships refers to the post ID.
INNER JOIN wp_term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id: Joins wp_term_relationships with wp_term_taxonomy to get details about the taxonomy (e.g., ‘product_tag’).
INNER JOIN wp_terms AS t ON tt.term_id = t.term_id: Joins wp_term_taxonomy with wp_terms to get the actual tag name and slug.
WHERE p.post_type = 'product': Filters for posts that are specifically WooCommerce products.
AND p.post_status = 'publish': Filters for published products. Adjust this if you need to include other statuses.
AND tt.taxonomy = 'product_tag': Ensures that we are specifically looking for product tags.
AND t.slug = 'your-product-tag-slug': This is the crucial part. Replace 'your-product-tag-slug' with the actual slug of the product tag you want to filter by. You can find the slug of a tag in the WordPress admin under Products > Tags.

This query will return the product_id, product_name, and product_status for all published WooCommerce products that have the specified product tag.

Bài viết cùng chuyên mục, chủ đề