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:
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.
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.