Blank structs for selecting records with left joined subquery

Question

I have a few simiple Ecto structs:

defmodule MyApp.ForumCategory do
  use MyApp.Schema

  schema "forum_categories" do
    field :name, :string

    field :last_topic, :map, virtual: true
    field :last_post, :map, virtual: true
  end
end

defmodule MyApp.ForumTopic do
  use MyApp.Schema

  schema "forum_topics" do
    field :name, :string

    timestamps()

    belongs_to :forum_category, MyApp.ForumCategory

    has_many :forum_posts, MyApp.ForumPost
  end
end

defmodule MyApp.ForumPost do
  use MyApp.Schema

  schema "forum_posts" do
    field :text, :string

    timestamps()

    belongs_to :profile, MyApp.Profile
    belongs_to :forum_topic, MyApp.ForumTopic
  end
end

And what I am trying to do is to retrieve the list of all forum categories with their last topic and this topic's last post:

...
  def list do
    topics_query =
      ForumTopic
      |> join(:inner, [ft], fp in assoc(ft, :forum_posts))
      |> distinct([ft], ft.forum_category_id)
      |> order_by([ft, fp], [desc: ft.forum_category_id, desc: fp.inserted_at])

    posts_query =
      ForumPost
      |> distinct([fp], fp.forum_topic_id)
      |> order_by([fp], [desc: fp.forum_topic_id, desc: fp.inserted_at])

    categories =
      ForumCategory
      |> join(:left, [fc], lft in subquery(topics_query), lft.forum_category_id == fc.id)
      |> join(:left, [fc, lft], lfp in subquery(posts_query), lfp.forum_topic_id == lft.id)
      |> join(:left, [fc, lft, lfp], lfpp in assoc(lfp, :profile))
      |> select([fc, lft, lfp, lfpp], {fc, lft, lfp, lfpp})
      |> Repo.all()
      |> Enum.map(&fold_category_data/1)

    {:ok, %{forum_categories: categories}}
  end

  defp fold_category_data({fc, nil, nil, nil}), do: fc
  defp fold_category_data({fc, lft, lfp, lfpp}) do
    fc
    |> Map.put(:last_topic, lft)
    |> Map.put(:last_post, %{lfp | profile: lfpp})
  end
...

But what I find strange is that if the forum category has no topics (and therefore posts either), query bindings lft and lfp are not selected as nil but instead they are selected as ForumTopic and ForumPost structs with all of their fields having nil and therefore my folding function fails.

But if I remove the subqueries and instead do something like that:

...
|> join(:left, [fc], lft in ForumTopic, lft.id == last_topic_id(fc.id))
...

where last_topic_id is a fragment macro that triggers a subquery for finding the last topic ID, then everything works as expected and I get nils instead of blank schemas.

Could somebody please explain why things work this way and what would be the best way to go around it?

PS I don't like the latter option because it involves writing big fragments and probably much more slower from the SQL performance point of view.

Aleksei Matiushkin Aleksei Matiushkin · Accepted Answer · 2019-04-12T05:49:17

I am positive that might be directly reported as an issue to Ecto. I am not a daily Ecto user, so I cannot guess why this decision was taken if it was done on purpose, but it looks pretty much like a bug to me.

The workaround would be very simple:

- defp fold_category_data({fc, nil, nil, nil}), do: fc
+ defp fold_category_data(
+   {fc, %ForumTopic{id: nil}, %ForumPost{id: nil}, nil}
+ ), do: fc

Blank structs for selecting records with left joined subquery

1 Answers