Day 7

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 CocoaCar do
  use Ecto.Schema

  @primary_key {:car_id, :id, autogenerate: true}
  schema "cocoa_cars" do
    field(:available_mixins, {:array, :string})
    field(:total_stock, :integer)
  end
end

defmodule Passenger do
  use Ecto.Schema

  @primary_key {:passenger_id, :id, autogenerate: true}
  schema "passengers" do
    field(:name, :string, source: :passenger_name)
    field(:favorite_mixins, {:array, :string})
    belongs_to(:car, Car)
  end
end

Query

# from(c in CocoaCar)
# from(p in Passenger)

remaining_cars =
  from(
    c in CocoaCar,
    order_by: [desc: :total_stock],
    limit: 3
  )

from(
  p in Passenger,
  join: c in subquery(remaining_cars),
  on: fragment("? && ?", p.favorite_mixins, c.available_mixins),
  group_by: p.name,
  order_by: p.name,
  select: [
    passenger_name: p.name,
    available_cars: fragment("ARRAY_AGG(? ORDER BY RANDOM())", c.car_id)
  ]
)
|> Repo.all()
|> Kino.DataTable.new(formatter: fn
  :available_cars, val -> {:ok, inspect(val, charlists: :as_lists)}
  _, _ -> :default
end)