preload_example.exs

# before this:
#
# createdb preload_example
#
# You may need to amend the `Repo.start_link` call with a username and password, depending on your
# Postgres configuration.

############################
## Deps and Repo boilerplate
############################

Mix.install([
  {:ecto, "~> 3.11"},
  {:ecto_sql, "~> 3.11"},
  {:postgrex, "~> 0.18.0"}
])

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

alias Example.Repo

{:ok, _} = Repo.start_link(database: "preload_example")

##############################
## DDL and Schema Definitions
##############################

tables_ddl = [
  """
  CREATE TABLE IF NOT EXISTS shipments (
    id bigserial primary key,
    name text not null,
    inserted_at timestamptz,
    updated_at timestamptz
  )
  """,
  """
  CREATE TABLE IF NOT EXISTS comments (
    id bigserial primary key,
    shipment_id bigint references shipments not null,
    author_name text not null,
    body text not null,
    commented_at timestamptz not null,
    inserted_at timestamptz,
    updated_at timestamptz
  )
  """,
  """
  CREATE TABLE IF NOT EXISTS events (
    id bigserial primary key,
    shipment_id bigint references shipments not null,
    source text not null,
    type text not null,
    message text not null,
    occurred_at timestamptz not null,
    inserted_at timestamptz,
    updated_at timestamptz
  )
  """,
  "create index if not exists index_events_on_shipment_id on events (shipment_id)",
  "create index if not exists index_comments_on_shipment_id on comments (shipment_id)",
  "create unique index if not exists index_shipments_on_name on shipments (name)"
]

for ddl <- tables_ddl, do: Repo.query!(ddl)

defmodule Example.Shipment do
  use Ecto.Schema

  schema "shipments" do
    field(:name, :string)
    has_many(:comments, Example.Comment, preload_order: [asc: :commented_at])
    has_many(:events, Example.Event, preload_order: [asc: :occurred_at])
    timestamps(type: :utc_datetime)
  end
end

defmodule Example.Comment do
  use Ecto.Schema

  schema "comments" do
    belongs_to(:shipment, Example.Shipment)
    field(:author_name, :string)
    field(:body, :string)
    field(:commented_at, :utc_datetime)
    timestamps(type: :utc_datetime)
  end
end

defmodule Example.Event do
  use Ecto.Schema

  schema "events" do
    belongs_to(:shipment, Example.Shipment)
    field(:source, :string)
    field(:type, :string)
    field(:message, :string)
    field(:occurred_at, :utc_datetime)
    timestamps(type: :utc_datetime)
  end
end

##############################
## Some Seed Data
##############################

# You have to use a module like this to avoid Mix.install complaining about structs
defmodule Seeds do
  alias Example.Shipment
  alias Example.Comment
  alias Example.Event

  def run() do
    [Comment, Event, Shipment] |> Enum.map(&Repo.delete_all/1)

    seeds = [
      %Shipment{
        name: "1234",
        comments: [
          %{
            author_name: "Ben",
            body: "This looks like a problem",
            commented_at: ~U[2024-01-02T00:00:00Z]
          },
          %{author_name: "Jane", body: "I agree", commented_at: ~U[2024-01-02T00:10:00Z]}
        ],
        events: [
          %{
            source: "FEDEX",
            type: "CARRIER_STATUS",
            message: "Out for delivery",
            occurred_at: ~U[2024-01-01T00:00:00Z]
          },
          %{
            source: "FEDEX",
            type: "CARRIER_STATUS",
            message: "Delayed",
            occurred_at: ~U[2024-01-01T10:00:00Z]
          }
        ]
      }
    ]

    for seed <- seeds, do: Repo.insert!(seed)
  end
end

Seeds.run()

##############################
## Common Access Patterns
##############################

# Let's say we are on a page running:
#
# GET /shipments/1234

defmodule Example.FakeController do
  import Ecto.Query
  alias Example.Shipment

  def get(params) do
    # Basically my point is that doing this:
    shipment =
      Shipment
      |> preload([:events, :comments])
      |> Repo.get_by(name: params.shipment_name)

    assigns = %{shipment: shipment}

    ## And doing this:
    # shipment =
    #   Shipment |> Repo.get_by(name: params.shipment_name)

    # comments =
    #   Comment
    #   |> where(shipment_id: ^shipment.id)
    #   |> order_by(asc: :commented_at)
    #   |> Repo.all()

    # events =
    #   Event
    #   |> where(shipment_id: ^shipment.id)
    #   |> order_by(asc: :occurred_at)
    #   |> Repo.all()

    # assigns = %{shipment: shipment, events: events, comments: comments}
    ## is identical at the DB level, but the code is much simpler with preload.

    ## And doing it via joins is both unergonomic and inefficient from a data transfer standpoint.

    render(assigns)
  end

  def render(assigns) do
    event_dom =
      for event <- assigns.shipment.events do
        "<li>#{event.inserted_at} - #{event.source}: #{event.message}</li>\n"
      end

    comment_dom =
      for comment <- assigns.shipment.comments do
        "<li>#{comment.inserted_at} - #{comment.author_name}: #{comment.body}</li>\n"
      end

    """
    <html>
    <body>
    <h1>#{assigns.shipment.name}<h1>
    <h2>Events</h2>
    <ul>
    #{event_dom}
    </ul>

    <h2>Comments</h2>
    <ul>
    #{comment_dom}
    </ul>
    </body>
    </html>
    """
  end
end

IO.puts(Example.FakeController.get(%{shipment_name: "1234"}))