Wordpress SQL:获取帖子类别和标签 [英] Wordpress SQL: get post category and tags

问题描述

我想查询存储在MySQL数据库中的Wordpress数据，以便获得带有列的结果:

I want to query Wordpress data stored in a MySQL database, in order to get a result with columns:

1. post_id
2. 类别
3. 逗号分隔的标签

预期输出:

+---------------+----------+----------------+
| post_id       | category | tags           |
|---------------+----------+----------------+
| 213           | news     | tag1,tag2,tag3 |
+---------------+----------+----------------+

这是我尝试过的:

SELECT
    p.id,
    c.name,
    GROUP_CONCAT(t.`name`)
FROM wp_posts p
JOIN wp_term_relationships cr 
    on (p.`id`=cr.`object_id`)
JOIN wp_term_taxonomy ct 
    on (ct.`term_taxonomy_id`=cr.`term_taxonomy_id` and ct.`taxonomy`='category')
JOIN wp_terms c 
    on (ct.`term_id`=c.`term_id`)
JOIN wp_term_relationships tr 
    on (p.`id`=tr.`object_id`)
JOIN wp_term_taxonomy tt 
    on (tt.`term_taxonomy_id`=tr.`term_taxonomy_id` 
   and tt.`taxonomy`='post_tag')
JOIN wp_terms t 
    on (tt.`term_id`=t.`term_id`)

结果是，我得到了想要的列以及期望的内容，但是我只得到了一行.

As a result, I get the columns I want, with the expected content, but I only get one row.

我在做什么错了?

推荐答案

如注释中所述，我包括一个聚合函数，但没有"group by"子句.

As noted in the comments, I was including an aggregate function, but no "group by" clause.

现在这似乎可行(只需添加GROUP BY行):

Now this seems to work (just added the GROUP BY line):

SELECT
    p.id,
    p.post_name,
    c.name,
    GROUP_CONCAT(t.`name`)
FROM wp_posts p
JOIN wp_term_relationships cr
    on (p.`id`=cr.`object_id`)
JOIN wp_term_taxonomy ct
    on (ct.`term_taxonomy_id`=cr.`term_taxonomy_id`
    and ct.`taxonomy`='category')
JOIN wp_terms c on
    (ct.`term_id`=c.`term_id`)
JOIN wp_term_relationships tr
    on (p.`id`=tr.`object_id`)
JOIN wp_term_taxonomy tt
    on (tt.`term_taxonomy_id`=tr.`term_taxonomy_id`
    and tt.`taxonomy`='post_tag')
JOIN wp_terms t
    on (tt.`term_id`=t.`term_id`)
GROUP BY p.id


+---------------+----------+----------------+
| post_id       | category | tags           |
|---------------+----------+----------------+
| 213           | news     | tag1,tag2,tag3 |
+---------------+----------+----------------+
| 216           | whatever | tag2,tag3      |
+---------------+----------+----------------+

谢谢你草莓！

这篇关于Wordpress SQL:获取帖子类别和标签的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！