Ecto and preloading most recent record from association

Phoenix Forum Questions / Help
2

Hello!

@hubertlepicki, here is my example.

It is 1 query from Elixir side:

Repo.preload (Repo.all Post), post_version: from(pv in PostVersion, distinct: pv.post_id, order_by: [desc: pv.updated_at])

, but I think there will be always 2 queries from DB side, because you first should get most young post version for each post (from table post_versions), than get all posts (from table posts) with preloaded youngest post version.

priv/repo/migrations/create_post.exs

defmodule Puma.Repo.Migrations.CreatePost do
  use Ecto.Migration    

  def change do
    create table(:posts) do
      add :name, :string    

      timestamps
    end
    create unique_index(:posts, [:name])    

  end
end

priv/repo/migrations/create_post_version.exs

defmodule Puma.Repo.Migrations.CreatePostVersion do
  use Ecto.Migration  

  def change do
    create table(:post_versions) do
      add :name, :string
      add :post_id, references(:posts, on_delete: :nothing) 

      timestamps
    end
    create index(:post_versions, [:post_id])  

  end
end

mix ecto.migrate

web/models/post.ex

defmodule Puma.Post do
  use Puma.Web, :model    

  schema "posts" do
    field :name, :string
    has_many :post_version, Puma.PostVersion
    
    timestamps
  end   

  @doc """
  Builds a changeset based on the `struct` and `params`.
  """
  def changeset(struct, params \\ %{}) do
    struct
    |> cast(params, [:name])
    |> validate_required([:name])
    |> unique_constraint(:name)
  end
end

web/models/post_version.ex

defmodule Puma.PostVersion do
  use Puma.Web, :model    

  schema "post_versions" do
    field :name, :string
    belongs_to :post, Puma.Post   

    timestamps
  end   

  @doc """
  Builds a changeset based on the `struct` and `params`.
  """
  def changeset(struct, params \\ %{}) do
    struct
    |> cast(params, [:name])
    |> validate_required([:name])
  end
end

priv/repo/migrations/seeds.exs

import Ecto.Query   

alias Puma.{Repo, Post, PostVersion}    

for post <- ~w(first second third) do
  Repo.get_by(Post, name: post) || Repo.insert!(%Post{name: post})
end   

query = from p in Post, select: p.id
posts_ids = Repo.all query    

for post_id <- posts_ids do
  for post_version <- ~w(1 2 3 4 5) do
    Repo.insert!(%PostVersion{post_id: post_id, name: post_version})
    :timer.sleep(1000)
  end
end

mix run priv/repo/migrations/seeds.exs

iex -S mix

iex(1)> alias Puma.{Repo, Post, PostVersion}
[nil, nil, nil]

iex(2)> import Ecto.Query
nil

iex(3)> Repo.preload (Repo.all Post), post_version: from(pv in PostVersion, distinct: pv.post_id, order_by: [desc: pv.updated_at])
[debug] QUERY OK db=2.6ms
SELECT p0."id", p0."name", p0."inserted_at", p0."updated_at" FROM "posts" AS p0 []
[debug] QUERY OK db=4.4ms decode=0.1ms
SELECT DISTINCT ON (p0."post_id") p0."id", p0."name", p0."post_id", p0."inserted_at", p0."updated_at", p0."post_id" FROM "post_versions" AS p0 WHERE (p0."post_id" = ANY($1)) ORDER BY p0."post_id", p0."post_id", p0."updated_at" DESC [[9, 8, 7]]
[%Puma.Post{__meta__: #Ecto.Schema.Metadata<:loaded>, id: 7,
  inserted_at: #Ecto.DateTime<2016-05-06 16:37:56>, name: "first",
  post_version: [%Puma.PostVersion{__meta__: #Ecto.Schema.Metadata<:loaded>,
    id: 35, inserted_at: #Ecto.DateTime<2016-05-06 16:38:00>, name: "5",
    post: #Ecto.Association.NotLoaded<association :post is not loaded>,
    post_id: 7, updated_at: #Ecto.DateTime<2016-05-06 16:38:00>}],
  updated_at: #Ecto.DateTime<2016-05-06 16:37:56>},
 %Puma.Post{__meta__: #Ecto.Schema.Metadata<:loaded>, id: 8,
  inserted_at: #Ecto.DateTime<2016-05-06 16:37:56>, name: "second",
  post_version: [%Puma.PostVersion{__meta__: #Ecto.Schema.Metadata<:loaded>,
    id: 40, inserted_at: #Ecto.DateTime<2016-05-06 16:38:05>, name: "5",
    post: #Ecto.Association.NotLoaded<association :post is not loaded>,
    post_id: 8, updated_at: #Ecto.DateTime<2016-05-06 16:38:05>}],
  updated_at: #Ecto.DateTime<2016-05-06 16:37:56>},
 %Puma.Post{__meta__: #Ecto.Schema.Metadata<:loaded>, id: 9,
  inserted_at: #Ecto.DateTime<2016-05-06 16:37:56>, name: "third",
  post_version: [%Puma.PostVersion{__meta__: #Ecto.Schema.Metadata<:loaded>,
    id: 45, inserted_at: #Ecto.DateTime<2016-05-06 16:38:10>, name: "5",
    post: #Ecto.Association.NotLoaded<association :post is not loaded>,
    post_id: 9, updated_at: #Ecto.DateTime<2016-05-06 16:38:10>}],
  updated_at: #Ecto.DateTime<2016-05-06 16:37:56>}]
8 Likes