mysql - 查询所需的支持

Question

我需要帮助来完成 Woocommerce 产品列表的 SQL 查询。我需要这个用于 wpdatatable sql 输入。

我搜索了示例代码并改编了找到的代码:

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_Posts

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_postmeta-

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_term_relationship

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

wp_term_taxonomy

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

您的查询无效，因为选择列表与 GROUP BY 不匹配。即使您的 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;