Skip to content

Instantly share code, notes, and snippets.

@wheeyls
Last active December 23, 2015 13:49
Show Gist options
  • Save wheeyls/6644175 to your computer and use it in GitHub Desktop.
Save wheeyls/6644175 to your computer and use it in GitHub Desktop.
Using ruby to grab an intersection of items in a list of categories.
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 )
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment