Day 12

# Configure postgrex to use the build-in JSON library before it's compiled
Application.put_env(:postgrex, :json_library, JSON)

Mix.install([
  {:ecto_sql, "~> 3.10"},
  {:postgrex, ">= 0.0.0"},
  {:kino, "~> 0.18.0"}
])

Kino.configure(inspect: [charlists: :as_lists])

Application.put_env(:myapp, Repo,
  url: "ecto://postgres:postgres@localhost/advent_of_sql_2025"
)

defmodule Repo do
  use Ecto.Repo, adapter: Ecto.Adapters.Postgres, otp_app: :myapp
end

{:ok, _pid} = Repo.start_link()

Setup

import Ecto.Query

defmodule ArchiveRecord do
  use Ecto.Schema

  schema "archive_records" do
    field :title, :string
    field :description, :string
  end
end

Query

# from(b in ArchiveRecord)

query = "fly:*"

search =
  from(
    a in ArchiveRecord,
    select: map(a, [:id, :title, :description]),
    select_merge: %{
      vec:
        fragment("SETWEIGHT(TO_TSVECTOR(title), 'A') || SETWEIGHT(TO_TSVECTOR(description), 'B')")
    }
  )

from(
  s in subquery(search),
  where: fragment("vec @@ TO_TSQUERY(?)", ^query),
  select: [
    title: s.title,
    description: s.description,
    rank: fragment("TS_RANK_CD(vec, TO_TSQUERY(?))", ^query)
  ],
  order_by: [
    desc: fragment("TS_RANK_CD(vec, TO_TSQUERY(?))", ^query),
    desc: :id
  ],
  limit: 5
)
|> Repo.all()
|> Kino.DataTable.new()