query_to_csv_concern.rb

require 'active_support/concern'
require 'csv'

module QueryToCSVConcern
  extend ActiveSupport::Concern
  
  # Dump ActiveRecord::Relation to a csv file.
  # Data will always order ascending based on first order parameter given
  # Ignores order and limit options like find_each
  # 
  # User.order('id').to_csv
  # User.search({email_contains => "barkbox"}).order('id').to_csv
  # User.select("users.*, addresses.*").
  #      joins("left join addresses on addresses.user_id = users.id").
  #      search({email_contains => "barkbox"}).
  #      order('id').
  #      to_csv
  def to_csv(options={})
    batch_size = options.delete(:batch_size) || 1000
    filename = "/tmp/#{self.klass.to_s.underscore.gsub('/', '__')}_#{Time.now.utc.strftime('%s')}"
    output = File.open("#{filename}.csv", "a+")

    paginate_on = "#{self.klass.table_name}.id"

    offset = 0
    primary_key_index = -1
    while true
      query = self.where(["#{paginate_on} > #{offset}"]).
                   order("#{paginate_on} ASC").
                   limit(batch_size).
                   to_sql
      result = ActiveRecord::Base.connection.execute(query)
      break if result.ntuples == 0

      # dump the headers and locate primary id the first time through
      if offset == 0  
        output << result.fields.to_csv
        primary_key_index = result.fields.index("#{self.klass.table_name}.id") || 
                            result.fields.index("#{self.klass.table_name}_id") ||
                            result.fields.index('id')
        if primary_key_index.nil?
          raise "Primary key not included in the custom select clause. Include #{self.klass} id"
        end
      end

      result.each_row do |row|
        offset = row[primary_key_index].to_i
        output << row.to_csv
      end
    end
    output.close
    filename
  end
end