来自自定义帖子类型的术语和子术语查询

时间:2015-05-10 作者:James Cook

我为一个招聘网站的商品目录创建了一个自定义的帖子类型和分类法。这很好用。我正在尝试生成一个列出所有项目及其类别和子类别的报告。我这样做是为了生成一个包含主要类别的表:

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

1 个回复
SO网友:Dave Hilditch

问题是,子类别中可能有1、2、3、4或N个深度级别。因此,编写查询是可能的,但效率很低,因为要做到这一点,需要使用速度不快的左连接。

结束

相关推荐

为什么自定义MySQL查询不返回结果?语法正确吗?

第一次进行自定义查询。我在寻找一种方法来获取某个分类法的类别列表,以便可以为自动完成函数运行类似的查询。为了得到一些结果,我提出了mysql查询,它在phpMyAdmin中给出了结果SELECT * FROM wp_terms INNER JOIN wp_term_taxonomy ON ( wp_terms.term_id = wp_term_taxonomy.term_id ) WHERE wp_terms.name LIKE \'ca%\' AND count