Ruby on Rails implementation of a ranking system using PostgreSQL

Hello, this article will show, how easily you can add ranking functionality to your project. We will accomplish it, with the use of ntile() window function. I will be using Ruby on Rails for my application, with PostgreSQL database.

We will need:

• A model with integer attribute, by which we want to make the ranking (in my case, it’s visit_count). Here is an exemplary migration for Post model:

  1class CreatePosts < ActiveRecord::Migration[5.0]
  2  def change 
  3   create_table :posts do |t|
  4  
  5       t.timestamps
  6       t.string :title
  7       t.integer :visit_count
  8       t.string :description
  9    end
  10  end
  11end

  tsx

• Controller

1class PostsController < ApplicationController 
2  def index 
3    @posts = # We will retrieve our collection here, but not now, we will get back to this later
4  end
5end

• Route for resource

1Rails.application.routes.draw do
2  resources :posts, only: :index
3end

• View for our index action and partial for post

1# index.html.erb
2<h1>Posts:</h1>
3<%= render @posts %>

1partial _post.html.erb
2<h3> 
3  Title: <%= post.title %>
4  Description: <%= post.description %> 
5  Visit count: <%= post.visit_count %> 
6  Popularity: <%= post.popularity %> # I will explain later, what is 'popularity' 
7</h3>

Here I won’t implement increment functionality for visit_count, because it's not the case of this article. We will just generate data by ourselves. For example by running this in Rails console:

120.times do |n|
2    Post.create(title: "Post nr#{n}", description: "Some description", visit_count: [*1..100].sample)
3end

Instead of adding method to Post model, I will just create Query Object called PostWithPopularityQuery, under app/query_objects/, to keep the model slim.

1 class PostWithPopularityQuery
2  POPULARITY_RANGES = 5
3
4  def self.call
5    Post.find_by_sql ['
6      SELECT
7        id, title, visit_count, description,
8      ntile(?) OVER (
9        ORDER BY visit_count ASC
10      ) AS popularity
11      FROM posts', POPULARITY_RANGES]
12  end
13end

You can see constant POPULARITY_RANGES. This will be the number of 'levels' of popularity/ranking you want. Let's say it's 5. The ntile() window function will then apply value in range 1-5 to each record. In other words, it will divide it in n-tiles.

About the public static .call method itself: first, we call method .find_by_sql on model (in this case it's Post), and we just open quotes and write SQL.

What about the SQL? We specify which columns we want to retrieve, then we use the ntile(?) with OVER - this will be the criteria for ranking, visit_count exactly. We also have to use order by, it is required for using ntile. There is also something called popularity in here. I named it this way, but you can name it as you want. By this name, you will retrieve it’s ranking value, by calling it on instance of record (in my case, it’s post.popularity)

And now, how do we know what popularity value (between 1 and 5) does each Post have? Simply call .popularity on Post instance retrieved by Query Object method.

How our controller will look like now? Simple:

1class PostsController < ApplicationController
2  def index
3    @posts = PostWithPopularityQuery.call
4  end
5end

With templates specified earlier, we see something like this when we visit our Posts#index

Of course you might want something prettier than plain number. With a little effort, you can change it to something cool, for example stars. I decided to add gem font-awesome-rails for this article demo. With one small change in our partial file _post.html.erb, like this:

1<h3>
2  Title: <%= post.title %>
3  Description: <%= post.description %>
4  Visit count: <%= post.visit_count %>
5  Popularity: <%- post.popularity.times do %>
6    <%= fa_icon "star-o" %>
7  <% end %>
8</h3>

I've achieved result like this: