如何在WordPress中选择包含特定标签/类别的帖子
问题描述:
这是一个非常具体的问题,关于mysql在中实现WordPress。如何在WordPress中选择包含特定标签/类别的帖子
我想开发一个插件,它会显示(选择)有特定的“标签”,属于特定的“类别的帖子(包括多个)
有人告诉我,这是不可能的,因为分类和标签的存储方式:
-
wp_posts
包含交的列表,每个岗位都有一个“身份证” -
wp_terms
包含术语列表(包括categorie s和标签)。选管会长期拥有TERM_ID -
wp_term_taxonomy
与他们TERM_IDs术语的列表,并为那些中的每一个分类定义(或者是分类或标签) -
wp_term_relationships
有关联中间人条款和岗位
如何加入表格以获取也属于“Category1”类别的标签“Nuclear”和“Deals”的所有帖子?
答
我误解了你。我以为你想要核能或交易。下面应该只给你核和交易。
select p.*
from wp_posts p, wp_terms t, wp_term_taxonomy tt, wp_term_relationship tr,
wp_terms t2, wp_term_taxonomy tt2, wp_term_relationship tr2
wp_terms t2, wp_term_taxonomy tt2, wp_term_relationship tr2
where p.id = tr.object_id and t.term_id = tt.term_id and tr.term_taxonomy_id = tt.term_taxonomy_id
and p.id = tr2.object_id and t2.term_id = tt2.term_id and tr2.term_taxonomy_id = tt2.term_taxonomy_id
and p.id = tr3.object_id and t3.term_id = tt3.term_id and tr3.term_taxonomy_id = tt3.term_taxonomy_id
and (tt.taxonomy = 'category' and tt.term_id = t.term_id and t.name = 'Category1')
and (tt2.taxonomy = 'post_tag' and tt2.term_id = t2.term_id and t2.name = 'Nuclear')
and (tt3.taxonomy = 'post_tag' and tt3.term_id = t3.term_id and t3.name = 'Deals')
答
什么是总数据库结构。无论如何,我会做这样的事情(注意我喜欢EXISTS加入,但是如果你喜欢,你可以将它们重新编写为连接;大多数查询分析器都会将它们折叠到同一个查询计划中)。您可能需要做一些额外的杂耍这种或那种方式,使其工作...
SELECT *
FROM wp_posts p
WHERE EXISTS(SELECT *
FROM wp_term_relationship tr
WHERE tr.object_id = p.id
AND EXISTS(SELECT *
FROM wp_term_taxonomy tt
WHERE tt.term_taxonomy_id = tr.term_taxonomy_id
AND tt.taxonomy = 'category'
AND EXISTS(SELECT *
FROM wp_terms t
WHERE t.term_id = tt.term_id
AND t.name = "Category1"
)
)
AND EXISTS(SELECT *
FROM wp_term_taxonomy tt
WHERE tt.term_taxonomy_id = tr.term_taxonomy_id
AND tt.taxonomy = 'post_tag'
AND EXISTS(SELECT *
FROM wp_terms t
WHERE t.term_id = tt.term_id
AND t.name = "Nuclear"
)
AND EXISTS(SELECT *
FROM wp_terms t
WHERE t.term_id = tt.term_id
AND t.name = "Deals"
)
)
)
答
试试这个:
select p.*
from wp_posts p,
wp_terms t, wp_term_taxonomy tt, wp_term_relationship tr
wp_terms t2, wp_term_taxonomy tt2, wp_term_relationship tr2
where p.id = tr.object_id
and t.term_id = tt.term_id
and tr.term_taxonomy_id = tt.term_taxonomy_id
and p.id = tr2.object_id
and t2.term_id = tt2.term_id
and tr2.term_taxonomy_id = tt2.term_taxonomy_id
and (tt.taxonomy = 'category' and tt.term_id = t.term_id and t.name = 'Category1')
and (tt2.taxonomy = 'post_tag' and tt2.term_id = t2.term_id and t2.name in ('Nuclear', 'Deals'))
基本上我使用相关子表2份 - 条款,term_taxonomy和term_relationship。一份适用'Category1'限制,另一份适用'Nuclear'或'Deals'限制。
顺便说一句,这是什么类型的项目,所有关于核交易的文章?你试图让我们在*名单上? ;)
答
Thanks @Eric it works!短短几年码校正以供将来参考:
- 第一选择语句错过wp_term_relationship TR2后昏迷
- 在同一选择statemt下列情况必须改变:
wp_terms t2, wp_term_taxonomy tt2, wp_term_relationship
tr2
应
wp_terms t3, wp_term_taxonomy tt3, wp_term_relationship
tr3
答
真的好棒的答案..帮了我很多..
伟大的bcoz。,它给了我构建我的复杂查询的基本方法!
一个小的修正,为用户准备像我这样:)
“wp_term_relationship”会给“不存在错误” ..使用wp_term_relationships,因为它是正确的表名。
谢谢埃里克