获取单个条目时获取查询排名 [英] Get ranking of query when getting a single entry

问题描述

我正在一个项目中，您喜欢帖子，喜欢的内容存储在表likes中，postId引用表$wpdb->posts(wordpress)中帖子的ID.我需要totalLikes，而rank后者是当前帖子的排名.

I'm working on a project where you like posts, where likes are stored in table likes with postId refering to the ID of the posts from table $wpdb->posts (wordpress). I need totalLikes and the rank latter being the ranking of the current post.

这在获取所有帖子时有效，但是当我需要一个帖子时，我将rank设置为1，而不是正确的排名，因为查询结果仅包含一个条目.

This works when getting all posts, but when I need a single post I get rank as 1, and not the correct ranking, because the query result only contains a single entry.

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

在获得单个帖子时如何获得排名?

What do I do to get the ranking while getting a single post?

推荐答案

您需要使用子查询:

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>;

请注意，这确实对性能有影响，因为子查询是物化的".但是，您已经必须进行排序，因此这应该增加一点额外的开销.

Note that this does have a performance implication, because the subquery is "materialized". However, you already have to do a sort so this should add little additional overhead.

这篇关于获取单个条目时获取查询排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！