# Joins configuration

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.

```elixir
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](#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 name="filtering"></a>

## 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.

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

Other `where` filters:

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

## Directly related tables

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`

```elixir
%{
  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`

```elixir
%{
  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:

```elixir
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"                  # <~~~
    }
  ]
}
```

<a name="limitations"></a>

## 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):`

```elixir
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.

```elixir
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](./testing.md)
for the cleanup pattern this requires.