Jason’s postings and stuff

It’s not uncommon in my application to maintain an exclusively preferred selection out of a collection on an object. For example:

class Person < ActiveRecord::Base
  has_many :employers, :class_name => 'Employee'
end
 
class Company < ActiveRecord::Base
  has_many :employees
end
 
class Employee < ActiveRecord::Base
  belongs_to :person
  belongs_to :company
 
  named_scope :favorite, {:conditions => {:favorite => true}}
  named_scope :without_me, lambda {|id|
    {:conditions => ['id NOT IN (?)', id]}
  }
 
  after_save :unique_favorite
end

Imagine there exists a possible principal employer and many ancillary employers. How then to allow for zero or one favorite employer, or email address, ect? (As opposed to weighting employers, perhaps by an integer value.) While likely not the most efficient or possibly even sane solution, I initially used the following after_save callback:

def unique_favorite
  person.employers(:reload)
  self.class.send(:with_scope, :find => {:conditions => 'favorite IS TRUE'}) do
    if person.employers.count > 0 and favorite?
      person.employers.reject {|myself| self == myself}.each do |employer|
        employer.update_attributes!({:favorite => false})
      end
    end
  end
end

First, the Person instance’s employers collection is reloaded. I found this necessary. Next, I define a scope and execute a count query on the employers association to determine if any favorites are already defined and if this employer instance is flagged as favorite.

If such is the case, all other employers with favorite set to true are set to false to ensure only one is true at a time.

Otherwise, nothing happens.

After an age, named_scope was introduced to ActiveRecord, making the following a cleaner callback:

def unique_favorite
  person.employers(:reload)
  if favorite? and person.employers.favorite.count > 0
    person.employers.without_me(self.id).each do |employer|
      employer.update_attributes!({:favorite => false})
    end
  end
end

Naturally, it can be taken a step further with an assist from PostgreSQL’s plpgsql stored procedure language.

-- Ensure favorite is true for only a single row
CREATE OR REPLACE FUNCTION employees_favorite() RETURNS TRIGGER AS $employees_favorite$
BEGIN
	IF (NEW.favorite = FALSE) THEN
		RETURN NULL;
	END IF;
	UPDATE employees SET favorite = FALSE WHERE id IN (
		SELECT t2.id FROM employees AS t1
		LEFT JOIN employees AS t2 ON t2.person_id = t1.person_id
		WHERE t1.id = NEW.id AND t2.favorite IS TRUE AND t2.id NOT IN (NEW.id)
	);
	RETURN NULL;
END;
$employees_favorite$ LANGUAGE plpgsql;
 
DROP TRIGGER IF EXISTS employees_favorite ON employees;
CREATE TRIGGER employees_favorite AFTER UPDATE OR INSERT ON employees
	FOR EACH ROW EXECUTE PROCEDURE employees_favorite();

Finally, the database functions need to be created somehow. A custom Rake task fits the fill, inspired by the excellent db-populate for seeding application data.

desc 'Load database functions stored in db/functions'
task :functions do
  require 'erb'
  require 'active_record'
  config = YAML::load(ERB.new(IO.read(File.dirname(__FILE__) + '/../../config/database.yml')).result)
  db_adapter = RAILS_ENV || 'development'
  db = ActiveRecord::Base.establish_connection(config[db_adapter])
  conn = db.connection()
  Dir[File.join(Rails.root, 'db', 'functions', '*.sql')].sort.each do |defs|
    conn.execute(File.read(defs))
    puts "Loaded #{File.basename(defs)}"
  end
end

Personally, I prefer the above approach — although database centric — because it DRYs up my models in a rather extremely sense. It’s also faster and absolutely guaranteed. I also prefer the simpler query, which asks the database for all other employee rows that share the same person, have favorite set to true, and do not have the primary key of the row for which the trigger is executing. Any rows returned from the subquery are then modified by the UPDATE statement to have favorite set to false.

Of course, both the ActiveRecord based Ruby centric approach and the PostgreSQL approach proceed from the assumption that the above logic is a good approach to solving the exclusive favorite amongst many choices problem I face.

It’s certainly easy to work with on the application side, as I can easily toggle any given item as a favorite and know it will be set exclusively. I use the same approach for email addresses, telephone numbers, and physical mailing addresses. (Yes, they’re all has_many associations in my application, not fixed column centric.)