查询所需的支持

Question

我需要帮助来完成Woocommerce产品列表的SQL查询。我需要将其用于WPDataTableSQL输入。

我搜索了一个示例代码，并修改了找到的代码：

SELECT
  p.ID,
  p.post_title,
  p.post_content,
  p.post_excerpt,
  t.name AS product_category,
  t.term_id AS product_id,
  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
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'
GROUP BY p.ID,p.post_title

它可以工作，但没有可变产品的产品行。它只显示产品的主要变化。

产品牛排：我有6种不同的牛排作为自己的产品，价格不同，重量也不同。通过我的SQL查询，我只能看到主产品“牛排”，没有变化和变化价格。

表wp_员额

id   | post_title                     | post_type         | post_parent | post_name | regular_price |
-----+--------------------------------+-------------------+-------------+-----------+---------------|
4113 | Steaks                         | product           | 0           |
4119 | Steaks-Lungenbraten Steak 125g | product_variation | 4113        | steaks_4  |6              |
4120 | Steaks-Hüftsteak 200g          | product_variation | 4113        | steaks_5  |4,4            |
4121 | Steaks-Flankensteak 600g       | product_variation | 4113        | steaks_6  |8,4            |

表wp_Posteta-

Post_ID |meta_key                | meta_value         |
--------+----------------+----------------------------+---------
4113    |                        | 
4119    | attribute_pa_steaks    | lungenbraten-steak |
4119    | _price.                | 6                  |
4120    | attribute_pa_steaks    | hueftsteak         |
4120    | _price.                | 4,4                |
4121    | attribute_pa_steaks    | flanksteak         |
4121    | _price.                | 8,4                |

wp_术语_关系

object_id.  | term_taxonomy_id. | term_order |
------------+-------------------+------------+
4113        |    6              |    0       |
4113        |   296             |    0       |
4113        |   297             |    0       |
4113        |   298             |    0       |

wp_术语_分类法

term_taxonomy_id. | term_id  | taxonomy    | description. | parent |count |
------------------+----------+-------------+--------------+--------+------+
296               |   296    |  pa_steaks  |              |  0     | 1.   |
297               |   297    |  pa_steaks  |              |  0     | 1.   |
298               |   298    |  pa_steaks  |              |  0     | 1.   |

wp_terms

term_id     |  name              | slug               | term_group | 
------------+--------------------+--------------------+------------+
296         | Flanksteak         | flanksteak         | 0          |
297         | Hüftsteakk         | hueftsteak         | 0          |
298         | Lungenbraten Steak | Lungenbraten-steak | 0          |

Answer 1

您的查询无效，因为选择列表与分组依据不匹配。即使您的DBMS有缺陷并且没有引发错误，我也不建议使用这样的查询。它的结果可能是任意的（比如MySQL）。编写一个查询，明确您真正想要选择的数据。

因为您只需要键/值表wp_postmeta中的一个值，所以不需要聚合：

SELECT
  p.id,
  p.post_title,
  p.post_content,
  p.post_excerpt,
  t.name AS product_category,
  t.term_id AS product_id,
  tt.term_taxonomy_id AS tt_term_taxonomia,
  tr.term_taxonomy_id AS tr_term_taxonomia,
  pm1.meta_value AS price
FROM wp_posts p 
LEFT JOIN wp_postmeta pm1 ON pm1.post_id = p.ID AND pm1.meta_key = '_price'
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'
ORDER BY
  CASE WHEN p.post_parent = 0 THEN id else post_parent END,
  CASE WHEN p.post_parent = 0 THEN 1 else 2 END;