問題描述

我一直在嘗試創建一個返回參數的查詢:

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

這篇關于獲取所有產品、類別和元數據的 SQL 查詢 woocommerce/wordpress的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！