Top Ranked steem-ua Delegators

I threw this together this afternoon by pointing a Ruby on Rails app at my hivemind node so it can perform live queries. To me, it's more of a proof of concept for using hivemind. Here's what it does:

1. Gets a list of comments at exactly depth 1 (i.e.: a reply directly to the original post).
2. Filters on comments only by steem-ua.
3. Filters on comments with: "body LIKE '%your post is ranked at **#1**.%'"

That last filter ensures we're looking only for #1 ranks. If you want a different rank, the query looks for that instead.

Once I get that list of comments, I then ask hivemind to return the parent posts.

So the full query becomes:

SELECT "hive_posts".*
FROM "hive_posts"
WHERE "hive_posts"."depth" = 0
AND "hive_posts"."parent_id" IS NULL
AND ("hive_posts"."id") IN
  (SELECT "hive_posts"."parent_id"
    FROM "hive_posts"
    INNER JOIN "hive_posts_cache"
      ON "hive_posts_cache"."post_id" = "hive_posts"."id"
  WHERE "hive_posts"."depth" = 1
  AND "hive_posts"."parent_id" IS NOT NULL
  AND "hive_posts"."author" = 'steem-ua'
  AND (body LIKE '%your post is ranked at **#1**.%'))
ORDER BY "hive_posts"."id" DESC

Try it out: https://steem-ua-ranks.herokuapp.com/

Update: Plot twist, this uses hivemind-ruby under the hood.

The above SQL was generated in a Rails action like this:

class PostsController < ApplicationController
  def index
    @page = (params[:page].presence || '1').to_i
    @per_page = (params[:per_page].presence || '100').to_i

    @rank = (params[:rank].presence || '1').to_i
    rank_match = "body LIKE '%your post is ranked at **##{@rank}**.%'"
    rank_comments = Hive::Post.depth(1).where(author: 'steem-ua')
    rank_comments = rank_comments.joins(:cache)
    rank_comments = rank_comments.where(rank_match)
    
    @posts = Hive::Post.depth(0).where(id: rank_comments.select(:parent_id))
    @posts = @posts.order(id: :desc)
    @posts = @posts.paginate(page: @page, per_page: @per_page)
  end
end