Using Arel without Rails

posted by codders in code, mysql, rails, ruby

Arel is a Relational Algebra for Ruby – a handy way to create SQL queries programmatically without requiring a full ORM. I’ve been doing some work (generating reports) that requires complex queries but between which there’s enough shared functionality to warrant some modularity in the query generation.

Cutting to the chase… Arel requires a database connection. It prefers ActiveRecord (which is probably overkill), but you do at least not have to create all the Model objects normally associated with Rails:

#!/usr/bin/ruby
require 'thread'  # RubyGems/ActiveRecord compatibility fix
require 'rubygems'
require 'active_record'
require 'arel'

@config = # Load some DB config

# Connect to the database
ActiveRecord::Base.establish_connection(
  :adapter => 'mysql',
  :host => @config[:host],
  :username => @config[:username],
  :password => @config[:password],
  :database => @config[:database]
)

# Setup the engine for Arel
Arel::Table.engine = Arel::Sql::Engine.new(ActiveRecord::Base)

# Create an Arel table - should match a table in your database
@users = Arel::Table.new(:users)

all of which is easy enough, completely undocumented, and only requires reading the code of most of the library.

Now, though, you can go ahead and write some fun queries:

@gadgets = Arel::Table.new(:gadgets)
query = @users.project(@users[:id].count).
        join(@gadgets).on(@gadgets[:creator_id].eq(@users[:id])).
        project(@gadgets[:name]).
        group(@gadgets[:name]).
        order(@gadgets[:name].asc)

puts query.to_sql

which outputs the very reassuring “SELECT COUNT(`users`.`id`), `gadgets`.`name` FROM `users` INNER JOIN `gadgets` ON `gadgets`.`creator_id` = `users`.`id` GROUP BY `gadgets`.`name` ORDER BY `gadgets`.`name` ASC”. You can even execute it:

puts "Gadget\tUsers"
ActiveRecord::Base.connection.execute(query.to_sql).each_hash do |row|
  puts [ row["name"], row["count"] ].join("\t")
end

Simple as that. The library has some issues, and no documentation, but I’ve been playing with it on my fork over at Github and it seems pretty okay.

Enjoy :)