我为一个招聘网站的商品目录创建了一个自定义的帖子类型和分类法。这很好用。我正在尝试生成一个列出所有项目及其类别和子类别的报告。我这样做是为了生成一个包含主要类别的表:
SELECT
wp_posts.ID as ID,
wp_posts.post_title as post_title,
wp_terms.name as main_category,
wp_terms.term_id as main_category_id
FROM
wp_posts, wp_term_relationships, wp_terms, wp_term_taxonomy
WHERE
wp_term_relationships.object_id = wp_posts.ID
AND wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
AND wp_term_taxonomy.term_id = wp_terms.term_id
AND wp_term_taxonomy.parent = \'0\'
AND wp_posts.post_type = \'goods\'
AND wp_posts.post_status = \'publish\'
ORDER BY wp_terms.term_id ASC
我只是绕了一圈就好了。但我也想显示子类别,我可以通过在foreach php循环中放置另一个查询来实现这一点。
但是,我需要按主类别再按子类别排序。
所以我想我需要在一个查询中完成这一切。这一定是可能的。我尝试过在查询中连接表,但语法不正确。这是我最近试过的一个。
SELECT
wp_posts.ID as ID,
wp_posts.post_title as post_title,
s1.name as main_category,
s1.term_id as main_category_id,
s2.name as main_category,
s2.term_id as main_category_id
FROM
wp_posts, wp_term_relationships, wp_terms s1, wp_term_taxonomy
LEFT JOIN wp_terms s2
ON s1.term_id = s2.id
WHERE
wp_term_relationships.object_id = wp_posts.ID
AND wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
AND wp_term_taxonomy.term_id = s1.term_id
AND wp_term_taxonomy.parent = \'0\'
AND wp_posts.post_type = \'goods\'
AND wp_posts.post_status = \'publish\'
ORDER BY s1.term_id ASC