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.
Just a couple of words about my Rails installation:
- CentOS 6.2
- Ruby 1.9.3p125
- Rails 3.2.1
- PostgreSQL 9.0.7
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.
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:
, 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
And then we can use those “scopes” to do sorting by whatever columns we defined (
downloads_bitrate in our case):
The final result is a well-defined code, without ugly and non-portable SQL.