SQL 쿼리를 통해 모든 제품, 카테고리 및 메타데이터 우커머스/워드프레스를 얻을 수 있습니다.

SQL 쿼리를 통해 모든 제품, 카테고리 및 메타데이터 우커머스/워드프레스를 얻을 수 있습니다.

매개변수를 반환하는 쿼리를 만드는 데 어려움을 겪고 있습니다.

ID, post_title, post_content, 카테고리, ID_category, SKU, 이름, 종목, 가격

지금 이 순간 나는 이것을 가지고 있습니다.

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와 카테고리 이름을 얻는 것은 매우 어렵고 인터넷에서 정보를 찾을 수 없었습니다.

감사해요.

가지고 계셨군요.object_id에 가입하기term_taxonomy_id말도 안 되는 소리였어요

다음은 제가 생각하는 바입니다. 주의: 저는 wp 데이터베이스를 조회한 적이 없고 문서만 보고 있었습니다.

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

FWIW

저도 같은 일을 시작했습니다.그리고 저는 제품 카테고리를 추가하고 싶었고 구글이 저를 여기로 이끌었습니다.

다음 코드는 변형 제품뿐만 아니라 변형 제품을 보여줍니다.

SELECT 
post_id,
t.name AS product_category,
IF(p.post_parent = 0, p.ID, p.post_parent) AS post_parent,
(SELECT post_title FROM wp_posts WHERE id = pm.post_id) AS title,
(SELECT meta_value FROM wp_postmeta WHERE post_id = pm.post_id AND meta_key = "_price" LIMIT 1) AS price, 
(SELECT meta_value FROM wp_postmeta WHERE post_id = pm.post_id AND meta_key = "_regular_price" LIMIT 1) AS "regular price", 
(SELECT meta_value FROM wp_postmeta WHERE post_id = pm.post_id AND meta_key = "_stock" LIMIT 1) AS stock, 
(SELECT meta_value FROM wp_postmeta WHERE post_id = pm.post_id AND meta_key = "_stock_status" LIMIT 1) AS "stock status", 
IFNULL((SELECT meta_value FROM wp_postmeta WHERE post_id = pm.post_id AND meta_key = "_sku" LIMIT 1),
    (SELECT meta_value FROM wp_postmeta WHERE post_id = pm.post_id AND meta_key = "_custom_field" LIMIT 1)) as SKU,
IFNULL(SUBSTR( (SELECT meta_value FROM wp_postmeta WHERE post_id = pm.post_id AND meta_key = "_product_attributes" LIMIT 1),
    INSTR((SELECT meta_value FROM wp_postmeta WHERE post_id = pm.post_id AND meta_key = "_product_attributes" LIMIT 1), "is_variation")+16,1),1) AS "variation"

FROM `wp_postmeta` AS pm
JOIN wp_posts AS p ON p.ID = pm.post_id
JOIN wp_term_relationships AS tr ON tr.object_id = IF(p.post_parent = 0, p.ID, p.post_parent)
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 meta_key in ("_product_version")
AND p.post_status in ("publish")
AND IFNULL(SUBSTR((SELECT meta_value FROM wp_postmeta WHERE post_id = pm.post_id AND meta_key = "_product_attributes" LIMIT 1),
INSTR((SELECT meta_value FROM wp_postmeta WHERE post_id = pm.post_id AND meta_key = "_product_attributes" LIMIT 1), "is_variation")+16,1),0)=0

언급URL : https://stackoverflow.com/questions/39776601/sql-query-to-get-all-products-categories-and-meta-data-woocommerce-wordpress