gistfile1.rb

namespace :data do

  # on my slow machine takes 5m09.209s
  desc 'Set positions per parent_id, position, short_name via SQL'
  task :sql_set_positions => :environment do
    parents_sql = 'SELECT DISTINCT parent_id FROM units'
    children_sql = %q{
      SELECT
          id FROM units
      WHERE
          parent_id = ?
      ORDER BY
          CASE WHEN position IS NULL THEN 1 ELSE 0 END, position, short_name
    }
    update_position_sql = 'UPDATE units SET position = ? WHERE id = ?'
    parents_rs = Unit.connection.execute(parents_sql)
    parents_rs.each do |parent|
      parent_id = parent.first
      sql = Unit.send(:sanitize_sql, [children_sql, parent_id])
      children_rs = Unit.connection.execute(sql)
      position = 1
      children_rs.each do |child|
        sql = Unit.send(:sanitize_sql, [update_position_sql, position,
            child.first])
        Unit.connection.execute(sql)
        position += 1
      end
    end

    # WHERE parent_id = NULL doesn't work so handle separately
    sql = 'SELECT id FROM units WHERE parent_id IS NULL'
    children_rs = Unit.connection.execute(sql)
    position = 1
    children_rs.each do |child|
      sql = Unit.send(:sanitize_sql, [update_position_sql, position,
          child.first])
      Unit.connection.execute(sql)
      position += 1
    end
  end

end