Get ranking of query when getting a single entry

问题:

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.

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?

回答1:

You need to use a subquery:

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.