How can I get this query to discover multiple accounts more efficiently and play w / has_many well?

In my User model, I want to find if a user has multiple accounts (aka 'multis').

Users have sessions; sessions are configured for installation

• creator_id
  
  = the ID of the first user to log in and
• updater_id
  
  = ID of the last user whose session was logged as

(Both columns are indexed.)

If I find that the two are different when saving the session, I save the session for posterity (and this request), because that means the user is logged in as one and then logged in as another, re-multi. (To catch the recursive base case creator_id

is reset in the current session.)

Here's the code that does it:

class Session < ActiveRecord::SessionStore::Session
  attr_accessor :skip_setters

  before_save :set_ip
  before_save :set_user

    def set_user
      return true if self.skip_setters
      # First user on this session
      self.creator_id ||= self.data[:user_id] 
      # Last user on this session
      self.updater_id = self.data[:user_id] if self.data[:user_id] 
      if self.creator_id and self.updater_id and 
       self.creator_id != self.updater_id
        logger.error "MULTI LOGIN: User #{self.creator.login} and \
          #{self.updater.login} from #{self.ip}"

        # Save a copy for later inspection
        backup = Session.new {|dup_session| 
          dup_session.attributes = self.attributes
          # overwrite the session_id so we don't conflict with the 
          # current one & it can't be used to log in
          dup_session.session_id = ActiveSupport::SecureRandom.hex(16)
          dup_session.skip_setters = true
        }
        backup.save

        # Set this session to be single user again.
        # Updater is what the user looks for;
        # creator is the one that there to trigger this.
        self.creator_id = self.updater_id
      end
    end

  # etc... e.g. log IP
end

      

        
        
        
      

    

The following request is executed.

However, it is ugly, not very efficient, and doesn't work very well with Rails' merge methods. Keep in mind that sessions are an extremely large and heavily used table, and users are much the same.

I would like to change this to has_many association (so that all associative magic works); maybe :through =>

a :multi_sessions

. He must capture both directions of diversity, and not just one, as the current association.

How can this be improved?

class User < ActiveRecord::Base
 has_many :sessions, :foreign_key => 'updater_id'

 # This association is only unidirectional; it won't catch the reverse case
 # (i.e. someone logged in first as this user and then as the other)
 has_many :multi_sessions, :foreign_key => 'updater_id', 
  :conditions => 'sessions.updater_id != sessions.creator_id',
  :class_name => 'Session'
 has_many :multi_users, :through => :multi_sessions,
  :source => 'creator', :class_name => 'User'

 ...

 # This does catch both, but is pretty ugly :(
 def multis
  # Version 1
  User.find_by_sql "SELECT DISTINCT users.* FROM users \
    INNER JOIN sessions \
      ON (sessions.updater_id = #{self.id} XOR sessions.creator_id = {self.id}) AND \
         (sessions.updater_id = users.id XOR sessions.creator_id = users.id) \
    WHERE users.id !=  #{self.id}"
  # Version 2
  User.find(sessions.find(:all, :conditions => 'creator_id != updater_id', 
    :select => 'DISTINCT creator_id, updater_id').map{|x|
      [x.creator_id, x.updater_id]}.flatten.uniq - [self.id])
 end
end