Ruby on Rails: Sort Joined Tables by Count

When you need to sort a table by one or many count parameters, you are typically faced with a dilemma: either use explicit and non-portable SQL queries or try to find “Rails-way” of doing it.

I decided to make my code as portable as possible and not include anything too database-dependent in it. So here I will show how to do joined table sorting by count.

Overview

Just a couple of words about my Rails installation:

  • CentOS 6.2
  • Ruby 1.9.3p125
  • Rails 3.2.1
  • PostgreSQL 9.0.7

Data Model

Before we begin I show the data model which we will be working on:

Data Model

As you can see, it is quite simple. This is a system for dispatching remote downloads.

Here are two tables: downloads and collectors. Each collector has one or many downloads associated with it, and one or many users (which we omit for simplicity).

We would like to have an overview table showing us all collectors, with all downloads and bitrates of the respective downloads.

Data Table

We would like to sort this table by highlighted columns, such as IP, Updated, and Memory. But what is of particular interest for us are Downloads tot and Bitrate columns. This is where we need to join tables and to sort through results.

Using Scopes

As a quick sidenote: don’t name your model “Collector” or anything else which might be in conflict with existing Rails entities. It was too late for me to rename, so I had to place the full class name before each call, like this:

@collector = ActiveRecord::Base::Collector.find(params[:id])

def index

  ...

  @collectors = ActiveRecord::Base::Collector.order(
    params[:sort] + ', collectors.id ASC').paginate(:page => page)
end

, collectors.id ASC was added to maintain consistency in results. Without that, PostgreSQL would sort grouped columns differently every time. Also please note pagination enabled (will_paginate gem).

It works well as far as you don’t need to sort by joined table’s field. In this case we should employ a special Rails entity called scope. You can hear about it in more details in the official documentation.

Here is what we should add to our collectors model:

scope :downloads_count,
      joins('LEFT JOIN downloads ON downloads.collector_id = collectors.id')
        .select('collectors.*,
                 COUNT(collectors.id) AS downloads_total')
        .group('collectors.id, collectors.ip, collectors.uptime,
                collectors.status, collectors.host_os, collectors.host_la,
                collectors.storage_used, collectors.storage_free,
                collectors.memory_used, collectors.created_at,
                collectors.updated_at, collectors.is_blocked,
                collectors.checked_at')

scope :bitrate_count,
      joins('LEFT JOIN downloads ON downloads.collector_id = collectors.id')
        .select('collectors.*,
                 COALESCE(SUM(downloads.bitrate), 0) AS downloads_bitrate')
        .group('collectors.id, collectors.ip, collectors.uptime,
                collectors.status, collectors.host_os, collectors.host_la,
                collectors.storage_used, collectors.storage_free,
                collectors.memory_used, collectors.created_at,
                collectors.updated_at, collectors.is_blocked,
                collectors.checked_at')

And then we can use those “scopes” to do sorting by whatever columns we defined (downloads_total and downloads_bitrate in our case):

def index

  ...

  # Special sorting cases
  if params[:sort] == 'downloads_total'
    collectors_scope = ActiveRecord::Base::Collector.downloads_count
  elsif params[:sort] == 'downloads_bitrate'
    collectors_scope = ActiveRecord::Base::Collector.bitrate_count
  else
    collectors_scope = ActiveRecord::Base::Collector
  end
   @collectors = collectors_scope.order(
    sort + ', collectors.id ASC').paginate(:page => page)
end

The final result is a well-defined code, without ugly and non-portable SQL.