Using ruby to grab an intersection of items in a list of categories.

intersection.rb

class ManyToMany
  def intersection(relation, cat_ids)
    join = cat_ids.map do |cat_id|
      outer_join_clause(cat_id)
    end.join(' ')

    where = cat_ids.map do |cat_id|
      " cat#{cat_id}.id is not null "
    end.join(' and ')

    relation.joins(join).where(where)
  end

  private
    def outer_join_clause(cat_id)
      join_table = 'categories_survey_template_sections'
      join_alias = "catsts#{cat_id}"
      cat_table = 'categories'
      cat_alias = "cat#{cat_id}"
      sect_table = 'survey_template_sections'

    <<-SQL
      left outer join (
        #{join_table} #{join_alias} join #{cat_table} #{cat_alias} ON
        #{join_alias}.category_id = #{cat_alias}.id and
        #{cat_alias}.id = #{cat_id}
      ) on (#{sect_table}.id = #{join_alias}.survey_template_section_id)
    SQL
    end
end


# ManyToMany.new.intersection(Category, [2]).to_sql =>
#SELECT "survey_template_sections".* FROM "survey_template_sections"
# left outer join (
#   categories_survey_template_sections catsts2 join categories cat2 ON
#   catsts2.category_id = cat2.id and\n        cat2.id = 2
# ) on (survey_template_sections.id = catsts2.survey_template_section_id)
# WHERE ( cat2 is not null )