获取所有产品、类别和元数据的 SQL 查询 woocommerce/wordpress

2021-09-27 00:00:00 sql mysql wordpress woocommerce

我一直在尝试创建一个返回参数的查询:

I'm stuck trying to create a query that returns me the parameters:

ID、post_title、post_content、类别、ID_category、SKU、名称、库存、价格

ID, post_title, post_content, category, ID_category, SKU, name, stock, price

此刻我有这个:

SELECT 
 p.ID,
 p.post_title,
 `post_content`,
 `post_excerpt`,
 t.name AS product_category,
 t.slug AS product_slug,
 tt.term_taxonomy_id AS tt_term_taxonomia,
 tr.term_taxonomy_id AS tr_term_taxonomia,
 MAX(CASE WHEN pm1.meta_key = '_price' then pm1.meta_value ELSE NULL END) as price,
 MAX(CASE WHEN pm1.meta_key = '_regular_price' then pm1.meta_value ELSE NULL END) as regular_price,
 MAX(CASE WHEN pm1.meta_key = '_sale_price' then pm1.meta_value ELSE NULL END) as sale_price,
 MAX(CASE WHEN pm1.meta_key = '_sku' then pm1.meta_value ELSE NULL END) as sku 

 FROM wp_posts p LEFT JOIN wp_postmeta pm1 ON ( pm1.post_id = p.ID)
 LEFT JOIN   wp_term_relationships AS tr ON ( tr.object_id = p.ID )
 LEFT JOIN   wp_term_taxonomy AS tt ON ( tt.taxonomy = 'product_cat' AND tr.object_id = tt.term_taxonomy_id)
 LEFT JOIN   wp_terms AS t ON ( t.term_id = tt.term_id )
 WHERE p.post_type in('product', 'product_variation') AND p.post_status = 'publish' AND p.post_content <> ''
 GROUP BY p.ID,p.post_title

它正确地为我提供了产品和元数据，但我很难在此查询中获得类别 ID 和类别名称，而且我无法在网上找到信息.

And it is giving me correctly the products and the metadata but it is very difficult for me to get the category ID and the category name in this query and I haven't been able to find information on the net.

谢谢.

推荐答案

您让 object_id 加入到 term_taxonomy_id 中，这毫无意义.

You had object_id joining to term_taxonomy_id which made no sense.

这就是我认为应该是这样的 -- 警告:我从来没有查询过 wp 数据库，只是查看了文档.

Here is how I think it should be -- caveat: I've never queried a wp database and was just going by the documentation.

SELECT 
  p.ID,
  p.post_title,
  `post_content`,
  `post_excerpt`,
  t.name AS product_category,
  t.term_id AS product_id,
  t.slug AS product_slug,
  tt.term_taxonomy_id AS tt_term_taxonomia,
  tr.term_taxonomy_id AS tr_term_taxonomia,
  MAX(CASE WHEN pm1.meta_key = '_price' then pm1.meta_value ELSE NULL END) as price,
  MAX(CASE WHEN pm1.meta_key = '_regular_price' then pm1.meta_value ELSE NULL END) as regular_price,
  MAX(CASE WHEN pm1.meta_key = '_sale_price' then pm1.meta_value ELSE NULL END) as sale_price,
  MAX(CASE WHEN pm1.meta_key = '_sku' then pm1.meta_value ELSE NULL END) as sku 
FROM wp_posts p 
LEFT JOIN wp_postmeta pm1 ON pm1.post_id = p.ID
LEFT JOIN wp_term_relationships AS tr ON tr.object_id = p.ID
JOIN wp_term_taxonomy AS tt ON tt.taxonomy = 'product_cat' AND tt.term_taxonomy_id = tr.term_taxonomy_id 
JOIN wp_terms AS t ON t.term_id = tt.term_id
WHERE p.post_type in('product', 'product_variation') AND p.post_status = 'publish' AND p.post_content <> ''
GROUP BY p.ID,p.post_title

相关文章