SQL查询WooCommerce以获取产品，变化，具有库存水平和类别

发布于 2025-01-21 23:45:52 字数 397 浏览 0 评论 0原文

我有一个SQL查询，可以正确提取产品变化和库存水平，但是我没有产品类别。如何将其添加到此查询中？理想情况下，我想提取最特定的类别，而不是父级类别。

SELECT p.ID, p.post_title, p.post_excerpt, p.post_name, pm.meta_key, pm.meta_value 
FROM wp_posts p
INNER JOIN wp_postmeta pm ON pm.post_id = p.ID
WHERE p.post_type IN ('product_variation')
AND p.post_status = 'publish'
AND pm.meta_key IN (
'_stock')
ORDER BY p.post_title

感谢您提供的任何帮助！

原文

I have an SQL query that properly pulls Product Variations and stock levels, but I don't have the Product Category. How can I add that to this query? I want to pull the single most specific category instead of the parent category, ideally.

SELECT p.ID, p.post_title, p.post_excerpt, p.post_name, pm.meta_key, pm.meta_value 
FROM wp_posts p
INNER JOIN wp_postmeta pm ON pm.post_id = p.ID
WHERE p.post_type IN ('product_variation')
AND p.post_status = 'publish'
AND pm.meta_key IN (
'_stock')
ORDER BY p.post_title

Thanks for any help you can offer!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

小兔几 2025-01-28 23:45:52

您可以尝试添加一些额外的内部连接

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 (t.term_id = tt.term_id)

，因为术语/分类单元使用枢轴（-ish）表来存储每个帖子的术语信息。

您是否没有使用默认的WP_QUERY获取所有产品的原因？

you could try to add some extra inner joins

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 (t.term_id = tt.term_id)

because the terms/taxonmy uses a pivot(-ish) table to store the terms information of each post.

Is there a reason why you're not using the default WP_Query to get all the products?

回复收藏 0 原文

喜爱皱眉﹌ 2025-01-28 23:45:52

我使用的最终查询是：

SELECT p.post_parent, p.ID, p.post_title, p.post_excerpt, p.post_name, pm.meta_key, pm.meta_value, MAX(t.slug)
FROM wp_posts p
INNER JOIN wp_postmeta pm ON pm.post_id = p.ID
INNER JOIN wp_term_relationships AS tr ON (p.post_parent = tr.object_id)
INNER JOIN wp_term_taxonomy AS tt ON (tt.taxonomy = 'product_cat' AND tr.term_taxonomy_id = tt.term_taxonomy_id)
INNER JOIN wp_terms AS t ON (t.term_id = tt.term_id)
WHERE p.post_type IN ('product_variation')
AND p.post_status = 'publish'
AND pm.meta_key IN (
'_stock')
AND pm.meta_value IS NOT NULL
GROUP BY p.post_parent, p.ID, p.post_title, p.post_excerpt, p.post_name, pm.meta_key, pm.meta_value
ORDER BY p.post_title

唯一的缺点是它可以与父级类别一起加入，而不是子类别。理想情况下，它始终加入最低的儿童类别，因此最具体。

The final query I used was:

SELECT p.post_parent, p.ID, p.post_title, p.post_excerpt, p.post_name, pm.meta_key, pm.meta_value, MAX(t.slug)
FROM wp_posts p
INNER JOIN wp_postmeta pm ON pm.post_id = p.ID
INNER JOIN wp_term_relationships AS tr ON (p.post_parent = tr.object_id)
INNER JOIN wp_term_taxonomy AS tt ON (tt.taxonomy = 'product_cat' AND tr.term_taxonomy_id = tt.term_taxonomy_id)
INNER JOIN wp_terms AS t ON (t.term_id = tt.term_id)
WHERE p.post_type IN ('product_variation')
AND p.post_status = 'publish'
AND pm.meta_key IN (
'_stock')
AND pm.meta_value IS NOT NULL
GROUP BY p.post_parent, p.ID, p.post_title, p.post_excerpt, p.post_name, pm.meta_key, pm.meta_value
ORDER BY p.post_title

The only downside is that it may join with the Parent Category instead of the Child category. Ideally it always joins the lowest level child Category so it's most specific.

回复收藏 0 原文

~没有更多了~