Usage with Collect

Collect builds a continuously updated document table with aggregated data for display and search. When dealing with searches that span multiple tables, Collect can be used as a document-storage builder, flattening the data, so that searches run against a single table optimized for the query.

Why combine Collect and Refine

Similar to Collect, Refine can pull data from related tables. Refine's joins build facets from values one or more joins away, while Collect flattens related rows into a document. They serve complementary roles.

What Collect brings to Refine: Collect resolves joins and aggregations once, into a continuously maintained document table. Refine can then facet that flat table directly instead of re-walking the joins for every facet build. Collect also produces things Refine doesn't: a data payload for displaying results and a weighted search_vector for full-text search. And while Refine's joins reach related tables from a single source, Collect can unify independent sources (for example, books and recipes) into one document table. Collect therefore gives Refine a flat, search-ready, display-ready source - potentially spanning several source tables - to facet.

What Refine brings to Collect: Collect produces a searchable document table, but on its own it answers the question "which documents match this text?" Refine adds faceted navigation on top: fast filtering across categorical values with per-option counts. Users can search the documents and narrow them by facets, seeing how many results each filter would yield. Refine turns Collect's documents into a browsable, filterable result set.

In short: Collect keeps a denormalized, search-ready table current as the underlying data changes; Refine layers fast faceted filtering and counts over it - providing full-text search and faceted navigation over data drawn from many tables, while keeping everything up to date incrementally.

Installation

Follow Collect's installation instructions.

Schema

To query the documents table with Ecto, define an Ecto.Schema using Collect.Schema. It declares the composite primary key and correct column types, and is required for searching with Refine or paginating with Flop.

defmodule MyApp.Document do
  use Ecto.Schema
  use Flop.Schema

  import Collect.Schema

  collect_schema "documents" do
    field :user_id, :binary_id
    # ... configured columns, with Ecto types matching the Collect column types
  end
end

Usage

There are two tables to configure and maintain: Collect's document table and Refine's facets table. Data flows as follows:

Source tables → Document table → Facets table

Collect's document table is kept up to date whenever a source or joined table is updated (merged on demand).
Refine's facets table is kept up to date whenever the document table is updated (merged on demand).

The lifecycle functions could look as follows. Note that the order of the calls to Collect and Refine matters, as Refine depends on the document table existing and being up to date.

defmodule MyApp.Document do

  @documents_table "documents"
  @document_facets_table "document_facets"

  def create_document_table do
    document_config = document_table_config()
    Collect.create_document_table_if_not_exists(
      @documents_table, document_config, repo: MyApp.Repo
    )
    
    facets_config = facets_table_config()
    Refine.create_facets_table_if_not_exists(
      @document_facets_table, facets_config, repo: MyApp.Repo
    )
  end

  
  def sync_document_table do
    document_config = document_table_config()
    Collect.merge_deltas(
      @documents_table, document_config, repo: MyApp.Repo
    )

    facets_config = facets_table_config()
    Refine.merge_deltas(
      @document_facets_table, facets_config, repo: MyApp.Repo
    )
  end

  def delete_document_table do
    # As with syncing, order matters:
    # drop Refine's facets first (removing its triggers from the document table),
    # then Collect's document table.

    Refine.drop_facets_table(@document_facets_table, repo: MyApp.Repo)
    Collect.drop_document_table(@documents_table, repo: MyApp.Repo)
  end

  defp document_table_config do
    # See Collect documentation "Configuration"
  end
  
  defp facets_table_config do
    # See Refine documentation "Configuration"
  end
end

To merge document and search updates, you can use PubSub or call a central update function on resource CRUD changes:

def notify_resource_changed, do: MyApp.Document.sync_document_table()

Configuration

Collect's data_fields configuration option provides several ways to aggregate data. This is particularly useful for preparing facet values and labels for use by Refine, especially when source data contains localized texts that are maintained separately from their corresponding values.

Example

This example "book collection" application stores the language (or languages) in which each book is written.

Besides a book_languages join table that links books to languages, it maintains the source tables languages (storing the locale) and language_texts (storing the translated title for each locale).

defmodule MyApp.Language do
  use Ecto.Schema
  alias MyApp.LanguageText

  @primary_key {:id, Ecto.UUID, autogenerate: true}
  @foreign_key_type Ecto.UUID

  schema "languages" do
    field(:locale, :string)
    
    has_many(:language_texts, LanguageText, on_replace: :delete)
  end
end

defmodule MyApp.LanguageText do
  use Ecto.Schema
  use MyApp.Cldr.Trans, translates: [:title]
  alias MyApp.Language

  @primary_key {:id, Ecto.UUID, autogenerate: true}
  @foreign_key_type Ecto.UUID

  schema "language_texts" do
    field(:title, :string)
    translations(:translations)

    belongs_to(:language, Language, type: :binary_id)
  end
end

We want to combine this data for faceting, so that Collect's data column (jsonb) contains a map with the locale as the key and the label as the value:

"languages": {
  "en": "English",
  "fr": "French"
}

In Collect's configuration we can use aggregate: :map to store the data from the joined tables:

%{
  source_table: "books",
  data_fields: [
    %{
      field_name: "languages",
      aggregate: :map,
      key_column: "languages.locale",
      value_column: "language_texts.title",
      join_table: "language_texts",
      joins: [
        %{
          table: "book_languages",
          match: "book_languages.book_id",
          to: "books.id"
        },
        %{
          table: "languages",
          match: "languages.id",
          to: "book_languages.language_id"
        },
        %{
          table: "language_texts",
          match: "language_texts.language_id",
          to: "languages.id"
        }
      ]
    }
  ]
}

In Refine's configuration we extract the language data:

value_column - Collect's data column.
value_path - The key path into the data object where the map lives (here languages, one level deep).
object_as: :map - Treat the languages JSON object as a bare map.

%{
  source_table: "documents",
  facets: [
    %{
      facet_name: "language",
      facet_label: "Language",
      value_column: "data",
      value_path: "languages",
      object_as: :map
    }
  ]
}

Searching

Searching the document table works like searching any other source table (see Searching), with one addition: pass a query: over the document schema so the search can resolve the document table's composite primary key.

Using the books application above, to search books filtered by languages "French or German":

import Ecto.Query

Refine.search("document_facets", facets_table_config(),
  query: from(d in MyApp.Document),
  facets: %{"language" => ["fr", "de"]},
  repo: Repo
)

The query: uses the MyApp.Document schema defined above. Its composite (source, source_identity) primary key (declared by Collect. Schema) is what lets the search reconnect the facet results to the document table - which is why searching a Collect document table requires the schema.

← Previous Page Usage with Flop

Next Page → Testing apps that use Refine