DataMapper hacks to generate raw SQL

loader.rb

  # Yields the given models wrapped in Loader::Table objects that can be
  # used to write SQL. The block should return an array with the SQL as
  # the first element, and variable substitutions as the rest.
  #
  # Example
  #   using_sql(Article) {|a|
  #     ["SELECT #{a.*} FROM #{a} WHERE #{a.id} = ?", 1] 
  #   }
  def using_sql(*models)
    opts = models.extract_options!
    raw_select(yield(*models.inject([]) {|array, model| 
      array += [Loader::Table.new(model, array.select {|y| y.model == model }.size)]
    }), opts)
  end

  # Directly execute either a DataMapper collection query or raw SQL against
  # the database adapter, bypassing DataMapper. It returns structs rather
  # than DataMapper objects. This can be used for read-only queries for
  # a significant performance boost.
  #
  # You can choose the wrapper struct class to be used by passing the :wrapper
  # option. See Loader::Struct for details.
  def raw_select(query, opts = {})
    sql = case query
      when DataMapper::Collection then repository.adapter.send(:select_statement, query.query)
      else [query[0], query[1..-1]]
    end

    repository.adapter.select(sql[0], *sql[1]).map {|x| 
      (opts[:wrapper] || Loader::Struct::Base).from_struct(x)
    }
  end

loader_table.rb

module Loader
  # Used for abstracting table and column names when writing raw SQL.
  #
  # Example:
  #   a = Table.new(Article)
  #   u = Table.new(User)
  #  
  #   sql = <<-SQL
  #     SELECT #{a.*}, #{u[:first_name, :last_name]} 
  #     FROM #{a}
  #     INNER JOIN #{u} ON #{u.id} = #{a.user_id}
  #     WHERE state = #{a.enum(:state, :active)}
  #   SQL
  class Table < ::Struct.new(:model, :index)
    def to_s
      model.storage_name.dup.tap do |result|
        result << " as #{__alias}" if result != __alias
      end
    end

    def __alias
      model.storage_name.dup.tap do |result|
        result << "_#{index+1}" if index > 0
      end
    end

    def [](*attributes)
      Columns.new(attributes.map {|attribute|
        field = if attribute == :*
          '*'
        elsif attribute.is_a? Hash
          "#{model.properties[attribute.keys.first].try(:field)} AS #{attribute.values.first}"
        else
          model.properties[attribute].try(:field)
        end
        raise "Unknown field: #{__alias}##{attribute}" unless field
        "#{__alias}.#{field}"
      })
    end

    def enum(property, value)
      field = model.properties[property]
      raise "Unknown field: #{__alias}##{property}" unless field
      field.flag_map.invert[value]
    end

    def method_missing(method, *args)
      opts = args.pop
      if opts && opts[:as]
        self[method => opts[:as]]
      else
        self[method]
      end
    end
  end

  # Allows columns to be added together
  #
  # Example:
  #   u = Table.new(User)
  #   sql = <<-SQL
  #     SELECT #{u.name + u.email} FROM users
  #   SQL
  class Columns < ::Struct.new(:names)
    def +(other)
      self.names += other.names
      self
    end

    def to_s
      self.names.to_a.join(',')
    end
  end
end

struct.rb

module Loader
  # Light-weight structs to wrap query results with a bit of meta-data.
  # Technically these should all be ActiveModel compliant, but they're not
  # quite yet.
  module Struct
    class Base < OpenStruct
      def self.model_name
        raise "Must subclass LoaderStruct to use rails URL helpers" unless model
        ActiveModel::Name.new(model)
      end

      def self.model(klass = nil)
        @model = klass if klass
        @model
      end

      def self.slugged(attribute = nil)
        @slugged = attribute if attribute
        @slugged
      end

      def initialize(hash_or_struct = nil)
        case hash_or_struct
        when Hash then super
        when OpenStruct then super(hash_or_struct.send(:table))
        else raise("Unknown type: #{hash_or_struct.class}")
        end
      end

      def slugged
        send self.class.slugged if self.class.slugged
      end

      def to_param
        [slugged, id].compact.map {|x| x.to_s.slugorize }.join '-'
      end
    end

    class Article < Base
      model ::Article::Published
      slugged :title
    end
  end
end