product_division.rb

  def unsynced_scoring_sessions
    rel = self.class.where(id: id).joins(:scoring_sessions)
    group_by = ['scoring_sessions.id', 'scoring_sessions.name', 'scoring_sessions.active']

    del_rel = rel.joins('JOIN scoring_session_questions_sessions ssqs '\
                        'ON ssqs.scoring_session_id = scoring_sessions.id')
                 .joins('LEFT JOIN product_divisions_scoring_session_questions pdssq '\
                        'ON pdssq.scoring_session_question_id = ssqs.scoring_session_question_id '\
                        'AND pdssq.product_division_id = product_divisions.id')
                 .where('pdssq.product_division_id IS NULL')
    for_delete = del_rel.group(group_by).count
    for_delete = Hash[for_delete.map {|k, cnt|
      [k[0], { name: k[1], active: k[2], delete: cnt, delete_answered: 0 }]
    }]

    del_answered = del_rel.where('EXISTS (SELECT 1 FROM scoring_surveys ss '\
                                         'JOIN scoring_session_answers ssa ON ssa.scoring_survey_id = ss.id '\
                                         'AND ssa.scoring_session_question_id = ssqs.scoring_session_question_id '\
                                         'WHERE ss.scoring_session_id = scoring_sessions.id)')
                             .group(group_by).count
    del_answered.each do |k, cnt|
      for_delete[k[0]][:delete_answered] = cnt
    end

    for_insert = rel.joins('JOIN product_divisions_scoring_session_questions pdssq '\
                           'ON pdssq.product_division_id = product_divisions.id')
                    .joins('LEFT JOIN scoring_session_questions_sessions ssqs '\
                           'ON ssqs.scoring_session_id = scoring_sessions.id '\
                           'AND ssqs.scoring_session_question_id = pdssq.scoring_session_question_id')
                    .where('ssqs.scoring_session_question_id IS NULL').group(group_by).count
    for_insert = Hash[for_insert.map {|k, cnt| [k[0], { name: k[1], active: k[2], insert: cnt }] }]

    for_delete.merge(for_insert){|k, v1, v2| v1.merge(v2)}.map {|k, v| v.merge({id: k})}
  end