mysql - 使复杂的查询变得更加复杂(需要获取完整的术语路径而不是一级)

Question

我编写了一个查询来检索产品变体 ID 和相应的分类术语(词汇目录)。

那里

1. field_data_field_products包含field_products_product_id(产品变体)和相应的entity_id(产品，即某些field_products_product_id可以具有相同的entity_id)
2. taxonomy_term_data 包含 tid(术语 ID)和 name(术语名称)
3. field_data_field_catalog 包含 field_catalog_tid(术语 ID)和entity_id(产品)
4. taxonomy_term_hierarchy包含tid(术语id)和parent(父术语tid)

SELECT
  field_data_field_products.field_products_product_id AS p1,
  taxonomy_term_data.name AS p2
FROM taxonomy_term_data
INNER JOIN field_data_field_catalog
  ON taxonomy_term_data.tid = field_data_field_catalog.field_catalog_tid
INNER JOIN field_data_field_products
  ON field_data_field_catalog.entity_id = field_data_field_products.entity_id

我的查询(尽管可能未优化)检索产品变体 ID 和相应的分类术语。

但我有两级术语层次结构。所以，我得到的术语是级别 2。我想用“term_level_1/term_level_2”组合来代替它。我怎样才能做到这一点？是否可以在一个查询中实现？

Answer 1

不确定您想要输出什么，没有表格我无法测试它，但这应该为您提供一些基础代码:-

SELECT
  field_data_field_products.field_products_product_id AS p1,
  taxonomy_term_data.name AS p2
  c.field_products_product_id AS p1,
  a.name AS p2
FROM taxonomy_term_data
INNER JOIN field_data_field_catalog
  ON taxonomy_term_data.tid = field_data_field_catalog.field_catalog_tid
INNER JOIN field_data_field_products
  ON field_data_field_catalog.entity_id = field_data_field_products.entity_id
INNER JOIN taxonomy_term_hierarchy
ON taxonomy_term_data.tid = taxonomy_term_hierarchy.tid
INNER JOIN taxonomy_term_data a
ON a.tid = taxonomy_term_hierarchy.parent
INNER JOIN field_data_field_catalog b
  ON taxonomy_term_data.tid = b.field_catalog_tid
INNER JOIN field_data_field_products c
  ON b.entity_id = c.entity_id