Active Record Ruby on Rails

Ruby on Rails / ActiveRecord – order with distinct

Ruby on Rails / ActiveRecord – order with distinct January 17, 20188 Comments

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`

Having problems with refactory or Ruby on Rails?

Hit me on twitter or use contact form and let me know how can I help you!

Download free RSpec & TDD ebook

Do you want to earn more or jump to the next level in your company? Do you know that testing skills are one of the most desired skills? There is only first step: start testing and do it right. My ebook can help you. Subscribe to the newsletter to get a free copy of the book.

8 comments

  1. 1. You could use scopes.
    2. Your SQL query is not quite OK.
    3. In Rails 2 I was able to reproduce the same result be simply executing:
    Location.find(:all, :select => “DISTINCT locations.*”, :joins => :users, :conditions => { :users => {:active => true} }, :order => “locations.name ASC”), I’m pretty sure I would reproduce it in Rails 4.x or 5.x in similar way, the query produced is:
    SELECT DISTINCT locations.* FROM `locations` INNER JOIN `users` ON (`users`.`location_id` = `locations`.`id`) WHERE (users.enabled =1) ORDER BY locations.name ASC

    1. Rails 2 are 5 years old already so I don’t think it makes sense to compare it. I wrote code this way also to isolate logic for given case and make it reusable

      1. Here is the Rails 5 version, and Rails 2 is actually more than 10 years old:
        Location.joins(:user).distinct.where(users: {active: true}).order(:name)

        SELECT DISTINCT “locations”.* FROM “locations” INNER JOIN “users” ON “users”.”id” = “locations”.”user_id” WHERE “users”.”active” = 1 ORDER BY “locations”.”name” ASC

        Let me know if you need the version using scopes 😉

Leave a Reply

Your email address will not be published. Required fields are marked *