extend_expense_approvers_v2.rb

def account_approvers_except(account)
  User.where(<<-SQL, account_id: account.id, parent_account_id: account.parent_account_id)
    id IN (
      (
        SELECT account_memberships.user_id
        FROM account_memberships
        WHERE account_id = :account_id AND (is_approver OR is_admin)

        UNION ALL

        SELECT bk_memberships.user_id
        FROM bk_memberships
        WHERE account_id = :parent_account_id AND client_account_id = :account_id
      )

      EXCEPT

      SELECT account_memberships.user_id
      FROM account_memberships
      WHERE account_id IN (:account_id, :parent_account_id) AND suspended
    )
  SQL
end

def expense_approver_exists?(account)
  account_approvers_except(account).exists?(42)
end

def account_approvers_no_except(account)
  User.find_by_sql([<<-SQL, {account_id: account.id, parent_account_id: account.parent_account_id}])
    SELECT *
    FROM users
    WHERE users.id IN (
      (
        SELECT account_memberships.user_id
        FROM account_memberships
        WHERE account_id = :account_id AND (is_approver OR is_admin) AND NOT suspended

        UNION ALL

        SELECT bk_memberships.user_id
        FROM bk_memberships
        INNER JOIN account_memberships ON bk_memberships.user_id = account_memberships.user_id
        WHERE bk_memberships.account_id = :parent_account_id AND bk_memberships.client_account_id = :account_id
          AND NOT account_memberships.suspended
      )
    )
  SQL
end


def measure(seed: rand(1..100_000), number_of_accounts: 50)
  clients = Account.where.not(parent_account_id: nil).order('name').offset(seed).limit(number_of_accounts).to_a; nil
  print_cache = []
  print_after = -> (*args) { print_cache << args }

  Benchmark.bmbm do |x|
    x.report('with joins') do
      results = clients.map { |c| account_approvers_no_except(c).map(&:id).sort }
      print_after.call 'identify:', results.hash
    end

    x.report('with expect') do
      results = clients.map { |c| account_approvers_except(c).map(&:id).sort }
      print_after.call 'identify:', results.hash
    end

    x.report('check exists') do
      clients.each { |c| expense_approver_exists?(c) }
    end
  end

  print_cache.each(&method(:p))
  nil
end

measure seed: 100
# =>
# Rehearsal ------------------------------------------------
# with joins     0.100000   0.000000   0.100000 ( 14.014758)
# with expect    0.060000   0.004000   0.064000 ( 12.912034)
# check exists   0.024000   0.000000   0.024000 (  0.061079)
# --------------------------------------- total: 0.188000sec
#
#                    user     system      total        real
# with joins     0.072000   0.008000   0.080000 ( 12.844182)
# with expect    0.048000   0.016000   0.064000 ( 12.604033)
# check exists   0.020000   0.000000   0.020000 (  0.056008)
# ["identify:", 2225831894520285221]
# ["identify:", 2225831894520285221]
# ["identify:", 2225831894520285221]
# ["identify:", 2225831894520285221]

# The exists query is much faster that's why I prefer to use AR scope in order to reuse most of the query(and do it performant) for the both use-cases
# 1. listing the account approvers
# 2. checking if user is can be approver for an account