Selecto.Builder.Sql.Tagging (Selecto v0.3.16)
Many-to-many tagging SQL pattern generation for join table relationships.
Supports tagging patterns through intermediate join tables with aggregation and faceted filtering capabilities. Handles the complexity of many-to-many relationships while maintaining proper parameterization.
Phase 3: Full many-to-many implementation with tag aggregation and faceted filtering
supported-patterns
Supported Patterns
- Basic many-to-many joins: LEFT JOIN through intermediate table
- Tag aggregation: string_agg for comma-separated tag lists
- Faceted filtering: EXISTS subqueries for tag filtering
- Tag counting: COUNT-based filtering for minimum tag requirements
examples
Examples
# Basic tagging join: posts ↔ post_tags ↔ tags
config = %{
source: "tags",
join_table: "post_tags",
tag_field: "name",
main_foreign_key: "post_id",
tag_foreign_key: "tag_id"
}
# Generates SQL like:
# LEFT JOIN post_tags pt ON main.id = pt.post_id
# LEFT JOIN tags t ON pt.tag_id = t.id
# With string_agg(t.name, ', ') for aggregationLink to this section Summary
Functions
Build faceted tag filter using EXISTS subquery.
Build tag aggregation column SQL.
Build tag count column SQL.
Build tag count filter for minimum tag requirements.
Build many-to-many tagging join with intermediate table.
Link to this section Functions
build_faceted_tag_filter(config, tag_values, match_type \\ :any)
Build faceted tag filter using EXISTS subquery.
Generates EXISTS subqueries for filtering records that have specific tags. Supports both single tag and array-based tag filtering.
examples
Examples
# Single tag filter
build_faceted_tag_filter(config, "programming", :single)
# Multiple tags filter (ANY match)
build_faceted_tag_filter(config, ["elixir", "phoenix"], :any)
# Multiple tags filter (ALL required)
build_faceted_tag_filter(config, ["web", "backend"], :all)Returns: {where_clause_iodata, params}
build_tag_aggregation_column(tag_table_alias, tag_field, column_alias)
Build tag aggregation column SQL.
Generates string_agg expressions for displaying comma-separated tag lists. Handles NULL values and provides proper GROUP BY compatibility.
examples
Examples
build_tag_aggregation_column("tags", "name", "tag_list")
#=> "string_agg(tags.name, ', ') as tag_list"
build_tag_aggregation_column("categories", "title", "category_names")
#=> "string_agg(categories.title, ', ') as category_names"Returns: iodata for SELECT clause
build_tag_count_column(tag_table_alias, column_alias)
Build tag count column SQL.
Generates COUNT expressions for counting distinct tags per record. Useful for filtering by minimum tag requirements.
examples
Examples
build_tag_count_column("tags", "tag_count")
#=> "COUNT(DISTINCT tags.id) as tag_count"Returns: iodata for SELECT clause
build_tag_count_filter(config, arg)
Build tag count filter for minimum tag requirements.
Generates WHERE conditions that filter records based on the number of tags they have. Useful for finding "well-tagged" content.
examples
Examples
build_tag_count_filter(config, {:gte, 3}) # At least 3 tags
build_tag_count_filter(config, {:eq, 1}) # Exactly 1 tag
build_tag_count_filter(config, {:between, 2, 5}) # Between 2-5 tagsReturns: {where_clause_iodata, params}
build_tagging_join_with_aggregation(selecto, join, config, fc, p, ctes)
Build many-to-many tagging join with intermediate table.
Generates the double-JOIN pattern required for many-to-many relationships:
- Main table → intermediate join table
- Join table → tag table
Includes support for tag aggregation and proper GROUP BY handling.
parameters
Parameters
selecto: Main selecto structjoin: Join identifier (atom)config: Join configuration with tagging optionsfc: Current from clause iodatap: Current parameters listctes: Current CTEs list
Returns: {updated_from_clause, updated_params, updated_ctes}