Using Arel without Rails
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.