Search

Copy Markdown

To use the documents table in Ecto queries, it is useful to define an Ecto.Schema for it.

Ecto schema

The fields in the Ecto schema are determined by the Collect configuration, or by a combination of configurations, so let’s start there.

The following example configuration is set up to collect website URLs:

config = %{
  source_table: "websites",
  add_identity_column_if_not_exists: true,
  identity_column: "identity",
  language: "simple",
  data_fields: [
    %{field_name: "resource_id", value_column: "id"},
    %{field_name: "user_id", value_column: "user_id"},
    %{field_name: "title", value_column: "title"},
    %{field_name: "url", value_column: "url"},
    %{field_name: "description", value_column: "description"},
    %{field_name: "favorite", value_column: "favorite"},
    %{field_name: "updated_at", value_column: "updated_at"},
    %{field_name: "deleted_at", value_column: "deleted_at"}
  ],
  search_fields: [
    %{field_name: "title", weight: 1},
    %{field_name: "description", weight: 2},
    %{field_name: "url"}
  ],
  columns: [
    %{name: "resource_id", from: "resource_id", type: "uuid"},
    %{name: "user_id", from: "user_id", type: "uuid"},
    %{name: "favorite", from: "favorite", type: "boolean"},
    %{name: "updated_at", from: "updated_at", type: "timestamp"},
    %{name: "deleted_at", from: "deleted_at", type: "timestamp"}
  ],
  indexes: [
    %{columns: ["user_id", "favorite", "updated_at", "deleted_at"]}
  ]
}

From this configuration, we can determine which fields need to be configured in the Ecto.Schema:

The default Collect columns are not included in the configuration, but should be added to the schema:

  • source
  • source_identity
  • data
  • search_vector

Instead of specifying these fields individually, the default_collect_fields macro from Collect.Schema can be used.

Custom columns are listed under the config's columns key:

  • resource_id
  • user_id
  • favorite
  • updated_at
  • deleted_at

The corresponding Ecto.Schema is:

defmodule MyApp.DocumentSchema do
  use Ecto.Schema
  import Collect.Schema

  @primary_key false
  schema "documents" do
    default_collect_fields()
    
    # Specified columns
    field(:resource_id, :binary)
    field(:user_id, :binary)
    field(:favorite, :boolean)
    field(:updated_at, :utc_datetime)
    field(:deleted_at, :utc_datetime)
  end
end

Querying

An example search function looks like this:

def search_documents(source, search_params \\ %{}) do
  query = search_params["q"]
  limit = search_params["limit"] || 10
  offset = search_params["offset"] || 0

  from(MyApp.DocumentSchema, as: :document)
  |> scope_to_source(source)
  |> scope_to_query(query)
  |> limit(^limit)
  |> offset(^offset)
  |> Repo.all()
end

The helper functions used above are:

defp scope_to_source(query, source) when source in ["websites"] do
  query
  |> where([d], d.source == ^source)
end

defp scope_to_source(query, _), do: query

Match the search term against the tsvector column, using the ranking configured by weight. For simplicity, the tsvector language is hardcoded to simple here.

defp scope_to_query(query, term) when is_binary(term) and term != "" do
  query
  |> where(
    [d],
    fragment("? @@ to_tsquery('simple', ? || ':*')", d.search_vector, ^term)
  )
  |> order_by([d],
    desc: fragment("ts_rank(?, to_tsquery('simple', ? || ':*'))", d.search_vector, ^term)
  )
end

defp scope_to_query(query, _), do: query

Search results

A few notes on the shape of search results:

  • data contains a string map.
  • search_vector is nil when using the default_collect_fields macro, as this field is only used for querying.