Order with distinct in a Rails query

Ruby on Rails / Active Record

If you ever tried ordering unique results you probably know that it’s not possible to make this in a simple query because ActiveRecord requires to explicitly select each column you want to operate on. To better visualize this let’s consider following case:

The problem

Location.joins(:users).where(users: {enabled: true}).distinct.order('locations.name')

What we want to achieve is to display each location with at least one enabled user. Locations list should be also ordered by the name. This query is not working because it throws following error:

ActiveRecord::StatementInvalid: Mysql2::Error: Unknown column 'locations.name'

The solution

Basically, we need two queries here:

  1. Fetch locations with at least on enabled user
  2. Order locations by the name

We can do it in one query thanks to the from method provided by Active Record. In order to do this, we have to refactor our Location model a little bit. We would add two class methods to isolate queries logic. After introducing changes our model looks following:

class Location < ActiveRecord::Base
  has_many :users

  def self.enabled
    joins(:user).where(users: {enabled: true}).distinct
  end

  def self.by_name
    order(:name)
  end
end

If you want to read more about above refactory you can check the previous post about it.

Having such structure we can use from method:

Location.from(Location.enabled, :locations).by_name

Let’s take a closer look at a from method as it takes two arguments:

  1. Query that would be used in a FROM instruction
  2. Name for used subquery to be able to operate on it later

Our SQL query looks like this:

SELECT `locations`.* FROM(SELECT DISTINCT `locations`.* FROM `locations` INNER JOIN `users` ON `users`.`location_id` = `locations`.`id` WHERE `users`.`enabled` = 1) ORDER BY `locations`.`name`