I recently worked on a CRM-type web app that tracks accounts, customers, and jobs for service contractors. Businesses in this vertical are used to tracking each customer by a customer number and each job with a job number. The simplest way to handle this in Rails is to use the id column as the customer or job number. The problem with this approach is that it causes confusion with accounts when they discover gaps in their sequences. These gaps are of course caused by other accounts creating records.

Confusion is bad and so in this case the business need required the use of a column other than the id to track these sequences.

I started looking at the acts_as_list code. The nice thing about this code is that it allows scoping a sequence (which seems to solve the initial problem). The issue though is that the sequence adjusts when a record in the sequence is deleted. Not good.

In the end I wrote my own acts_as_sequenced ActiveRecord extension.

Here is how it works:

class Account < ActiveRecord::Base
end

class Customer < ActiveRecord::Base
    acts_as_sequenced :column => :customer_number, :scope => :account
end

class Job < ActiveRecord::Base
    acts_as_sequenced :column => :job_number, :scope => :account
end

Pretty simple. Put acts_as_sequenced in the /lib directory and include it environment.rb.

# environment.rb
require 'acts_as_sequenced.rb'

acts_as_sequenced.rb (Updated 8/8/2006 to include support for acts_as_paranoid plugin.)

module ActiveRecord
  module Acts 
    module List 
      def self.append_features(base)
        super
        base.extend(ClassMethods)
      end

      module ClassMethods
        # Configuration options are:
        #
        # * +column+ - specifies the column name to use for keeping the position integer (default: position)
        # * +scope+ - restricts what is to be considered a list. Given a symbol, it'll attach "_id" 
        #   (if that hasn't been already) and use that as the foreign key restriction. It's also possible 
        #   to give it an entire string that is interpolated if you need a tighter scope than just a foreign key.
        #   Example: <tt>acts_as_sequenced :scope => 'todo_list_id = #{todo_list_id} AND completed = 0'</tt>
        def acts_as_sequenced(options = {})
          configuration = { :column => "position", :scope => "1 = 1" }
          configuration.update(options) if options.is_a?(Hash)

          configuration[:scope] = "#{configuration[:scope]}_id".intern if configuration[:scope].is_a?(Symbol) && configuration[:scope].to_s !~ /_id$/

          if configuration[:scope].is_a?(Symbol)
            scope_condition_method = %(
              def scope_condition
                if #{configuration[:scope].to_s}.nil?
                  "#{configuration[:scope].to_s} IS NULL"
                else
                  "#{configuration[:scope].to_s} = #{#{configuration[:scope].to_s}}"
                end
              end
            )
          else
            scope_condition_method = "def scope_condition() \"#{configuration[:scope]}\" end"
          end

          class_eval <<-EOV
            include ActiveRecord::Acts::List::InstanceMethods

            def acts_as_sequenced_class
              ::#{self.name}
            end

            def position_column
              '#{configuration[:column]}'
            end

            #{scope_condition_method}

            before_create  :assign_next_number_in_sequence
          EOV
        end
      end

      module InstanceMethods

        private

          def assign_next_number_in_sequence
            if acts_as_sequenced_class.methods.include?('paranoid?') && acts_as_sequenced_class.paranoid?
              max = acts_as_sequenced_class.calculate_with_deleted(:max, position_column, :conditions => scope_condition)
            else
              max = acts_as_sequenced_class.maximum(position_column, :conditions => scope_condition)
            end
            self[position_column] = (max ? max : 0  ) + 1
          end

      end     
    end
  end
end