Joins configuration

Copy Markdown

The joins configuration option enables you to build facets from values in related tables instead of limiting them to columns on the source table itself.

A facet's value can come from a table one join away. For example, if articles references authors, the joins option makes author fields available as facets. It can also come from a table several joins away, reached through a chain of intermediate tables.

joins: [
  %{
    table: "roles",
    match: "roles.id",
    to: "authors.role_id"
  }
]

Each entry in joins describes one step in that chain: which table to bring in, and how it connects to a table already in the chain. By listing several steps, you can follow a path of any length from the source table to the table holding the facet value.

A common case is a many-to-many relationship:

  • articles → article_categories → categories

Refine keeps the facet data it reads from the joined tables automatically up to date. Inserts, updates, and deletes anywhere along the chain - including changes to the intermediate tables that link everything together - are tracked and reflected in the facet data. This covers renaming a joined row, attaching or detaching a related row, and deleting a joined row.

Because tracking happens at the database level, it applies whether changes are made through Ecto, raw SQL, or association casting.

See also the limitations section below.

Join options

The joins entry map can be read as:

  • Join table
  • ... matching its match column (on the entry's own table)
  • ... to the to column (on the source table or an earlier join)
  • ... where value is true or false (optional SQL filter, see Filtering ↓)

Filtering a joined table

Use where to apply an additional condition to a joined table - for example, to exclude soft-deleted rows.

It applies an SQL filter to the join, appended as an additional AND condition. The filter is raw SQL, appended as an AND to the join condition. So it's written in database terms (= true, <> 'archived', IS NOT NULL), not Ecto terms.

The filter may only reference the table introduced by its own entry.

%{
  table: "categories",
  match: "categories.id",
  to: "article_categories.category_id",
  where: "categories.active"
}

Other where filters:

where: "categories.name <> 'editorial'"
where: "article_categories.is_primary = true"
where: "tags.label IS NOT NULL"
where: "categories.created_at >= '2024-01-01'"

When the source table has a foreign key to the joined table, a single join is enough.

Steps in this example:

  • authors → roles → read role's name
%{
  facets_table: "authors_facets",
  source_table: "authors",
  add_identity_column_if_not_exists: true,
  identity_column: "identity",
  facets: [
    %{
      facet_name: "role_name",
      join_table: "roles",
      value_column: "name"
    }
  ],
  joins: [
    %{
      table: "roles",
      match: "roles.id",
      to: "authors.role_id"
    }
  ]
}

Intermediate tables

When the joined table is reached through a link table - for example a many-to-many relationship - declare each step as its own entry.

Steps in the example below:

  • articles → article_categories → categories → category_texts → read text's title
%{
  facets_table: "articles_facets",
  source_table: "articles",
  add_identity_column_if_not_exists: true,
  identity_column: "identity",
  facets: [
    %{
      facet_name: "category_texts",
      join_table: "category_texts",
      value_column: "title"
    }
  ],
  joins: [
    %{
      table: "article_categories",
      match: "article_categories.article_id",
      to: "articles.id"
    },
    %{
      table: "categories",
      match: "categories.id",
      to: "article_categories.category_id"
    },
    %{
      table: "category_texts",
      match: "category_texts.category_id",
      to: "categories.id"
    }
  ]
}

In this example:

  1. The first entry brings in article_categories, matching its article_id to articles.id on the source.
  2. The second entry brings in categories, matching its id to the category_id column on article_categories from the first entry.
  3. The third entry brings in category_texts, matching its category_id to categories.id.

Each new entry extends the chain by connecting one of its own columns back to a column already in scope.

Qualified table names

Every reference to a table in a non-default schema must include the schema prefix. Tables in the public schema are written without a prefix.

Add the non-default schema prefix to these options:

  • facets
    • join_table
  • joins
    • table
    • match
    • to

Example:

config = %{
  facets_table: facets_table,
  source_table: source_table,
  add_identity_column_if_not_exists: true,
  identity_column: "identity",
  facets: [
    %{
      facet_name: "category_texts",
      join_table: "classifications.category_texts",        # <~~~
      value_column: "title"
    }
  ],
  joins: [
    %{
      table: "article_categories",
      match: "article_categories.article_id",
      to: "articles.id"
    },
    %{
      table: "classifications.categories",                 # <~~~
      match: "classifications.categories.id",              # <~~~
      to: "article_categories.category_id"
    },
    %{
      table: "classifications.category_texts",             # <~~~
      match: "classifications.category_texts.category_id", # <~~~
      to: "classifications.categories.id"                  # <~~~
    }
  ]
}

Limitations and requirements

Joined facets are kept up to date using database triggers installed on both the source and joined tables. This introduces a number of requirements and constraints.

Joined table should use cascading deletes

Whether deleting a row from a joined table correctly updates facets depends on the foreign key relationships between the tables in the join chain.

For many-to-many joins, the foreign key from the join (link) table to the joined table should use cascading deletes.

In an Ecto migration this is configured using references(..., on_delete: :delete_all):

add :category_id, references(:categories, on_delete: :delete_all)

Without cascading deletes, removal of a referenced row in the joined table will be blocked by the database. As a result, facet updates cannot occur because the deletion itself does not take place.

Joins must use single-column foreign keys

Each join connects exactly one column to one column. Composite (multi-column) foreign keys are not supported. If a join's underlying foreign key spans more than one column, creating the facets table fails with an error.

Joins connect tables by matching equal columns

Each join links two tables by pointing at a column in each that holds the same value. In the example below, the join connects article_categories to articles wherever an article_categories row's article_id equals an articles row's id - the standard way a foreign key relates two tables.

match: "article_categories.article_id",
to: "articles.id"

This is the only kind of connection a join can express: one column equals another. You can't join on a range, a comparison, or a more complex condition (for example, "where the article's date falls within the category's active period"). If you need to narrow which rows a join includes, use a where filter on the join rather than trying to express it in the connection itself.

Chain depth

Joins through intermediate tables have been tested up to three steps:

  • source → intermediate → intermediate → leaf

Deeper chains use the same mechanism but are not yet covered by tests.

Testing requires committed data

Because tracking happens through database triggers, tests that exercise incremental updates need the sandbox in :auto mode - the triggers must see committed data, which a rolled-back transaction (:manual mode) does not provide. See the testing guide for the cleanup pattern this requires.