Composing SQL queries with Arel

I believe you agree that SQL queries as a long strings are not very readable. Fortunately, in the Ruby on Rails world there is an Arel. For context, let’s say we have the following models:

class Resource < ActiveRecord::Base
  belongs_to :environment
  belongs_to :permission
end

class Environment < ActiveRecord::Base
  has_many :resources
  has_and_belongs_to_many :permissions
end

class Permission < ActiveRecord::Base
  has_many :resources
  has_and_belongs_to_many :environments
end

As we see, the Resource belongs to the Environment and to the Permission. There is also a many-to-many relationship between Permission and Environment. The Permission defines which environments are allowed. So, the Resource is only available when its Environment is assigned to its Permission.

class Resource < ActiveRecord::Base
  #  (...)
  def available?
    permission.environment_ids.include?(environment_id)
  end
end

Our task is to get a list of all available resources. Arel comes in handy here.

Resource.joins(permission: :environments)
        .where(
          Environment.arel_table[:id]
                     .eq(Resource.arel_table[:environment_id])
        )

By using joins we got all the resources that have at least one allowed environment. Next, we had to filter these resources where the environment_id matches the id of the allowed environment. This gives us the following SQL query:

SELECT "resources".* 
FROM "resources" 
  INNER JOIN "permissions" 
    ON "permissions"."id" = "resources"."permission_id"
  INNER JOIN "environments_permissions" 
    ON "environments_permissions"."permission_id" = "permissions"."id" 
  INNER JOIN "environments" 
    ON "environments"."id" = "environments_permissions"."environment_id" 
WHERE "environments"."id" = "resources"."environment_id"

Arel version looks simpler, right? You can use Scuttle to translate your SQL to Arel.