php - 获取单个条目时获取查询的排名

Question

我正在做一个你点赞帖子的项目，点赞存储在表 likes 中postId 引用表 $wpdb->posts 中的帖子 ID (WordPress)。我需要 totalLikes和 rank后者是当前帖子的排名。

这在获取所有帖子时有效，但当我需要单个帖子时，我得到 rank为 1，而不是正确的排名，因为查询结果只包含一个条目。

SELECT @curRank := @curRank + 1 AS rank, $wpdb->posts.*, (SELECT COUNT(*) FROM likes l WHERE l.postId = $wpdb->posts.ID) as totalLikes
            FROM $wpdb->posts, (SELECT @curRank := 0) r
            WHERE $wpdb->posts.post_type = 'post'
            AND $wpdb->posts.post_status = 'publish'
            ORDER BY totalLikes DESC

如何在获得单个帖子的同时获得排名？

Answer 1

您需要使用子查询:

SELECT *
FROM (SELECT @curRank := @curRank + 1 AS rank, $wpdb->posts.*,
             (SELECT COUNT(*) FROM likes l WHERE l.postId = $wpdb->posts.ID) as totalLikes
      FROM $wpdb->posts CROSS JOIN (SELECT @curRank := 0) r
      WHERE $wpdb->posts.post_type = 'post' AND $wpdb->posts.post_status = 'publish'
      ORDER BY totalLikes DESC
     ) t
WHERE <whatever condition you want>;

请注意，这确实对性能有影响，因为子查询是“具体化”的。但是，您已经必须进行排序，因此这应该不会增加额外的开销。