php - WordPress查询基于自定义表获取帖子

我需要按父类别从数据库中获取一些常规的WordPress帖子，并按我在数据库中自定义表的结果进行排序。

我要为其（包括子级）检索帖子的父类别称为资源管理器，其ID为29。

自定义表称为wp_upvotes。该表中有几列，但我们关心的唯一列可能是id和postID。我想按wp_posts等于postID的行数对wp_post.ID进行排序，如果该表中没有其他帖子的行，则应按日期在末尾对它们进行排序。我希望按日期将最多的upvotes减少到最小的upvotes。

我尝试过的查询是这样的（它只返回第一篇文章，而不是全部）：

$catIDs = array(29,30,31,32);

SELECT wp_posts.*, COUNT(wp_upvotes.id) AS upvotes FROM wp_posts
LEFT JOIN wp_upvotes ON (wp_posts.ID = wp_upvotes.postID)
INNER JOIN wp_term_relationships
ON (wp_posts.ID = wp_term_relationships.object_id)
INNER JOIN wp_term_taxonomy
ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
AND wp_term_taxonomy.taxonomy = 'category'
AND wp_term_taxonomy.term_id IN (" . implode(',', $catIDs) . "))
AND wp_posts.post_status = 'publish'
ORDER BY upvotes DESC, wp_posts.post_date DESC

当我删除wp_upvotes表的LEFT JOIN时，它返回所有正确的帖子。为什么当我使用LEFT JOIN时它只返回一行？

SELECT wp_posts.* FROM wp_posts
INNER JOIN wp_term_relationships
ON (wp_posts.ID = wp_term_relationships.object_id)
INNER JOIN wp_term_taxonomy
ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
AND wp_term_taxonomy.taxonomy = 'category'
AND wp_term_taxonomy.term_id IN (" . implode(',', $catIDs) . "))
AND wp_posts.post_status = 'publish'
ORDER BY wp_posts.post_date DESC

最佳答案

看来主要因素是出于任何原因使用ORDER BY wp_posts.ID使其显示所有行而不是仅显示一行。奇怪的是，这是我上面要做的最终代码：

SELECT wp_posts.*, COUNT(wp_upvotes.id) AS upvotes FROM wp_posts
INNER JOIN wp_term_relationships
ON (wp_posts.ID = wp_term_relationships.object_id)
INNER JOIN wp_term_taxonomy
ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
AND wp_term_taxonomy.taxonomy = 'category'
AND wp_term_taxonomy.parent = 29)
LEFT JOIN wp_upvotes ON (wp_posts.ID = wp_upvotes.postID)
WHERE wp_posts.post_status = 'publish'
GROUP BY wp_posts.ID
ORDER BY upvotes DESC, wp_posts.post_date DESC

关于php - WordPress查询基于自定义表获取帖子，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/26921800/