Hi,

In my project, I have tables called Posts and Comments.
I have been given the requirement that each post will have multiple comments and each comment will have a post. Also each POST will have only one active comment.

So I have a schema defined like this.
post_schema.ex

  schema "post" do
    field :title, :string
    ...
    ...
    has_many :comments, Comment

    timestamps()
  end

comment_schema.ex

schema "comment" do
    field :message, :string
    ...
    ...
    field :is_active, :boolean
    field :is_active_last_updated, :utc_datetime

    belongs_to :post, Post, type: :binary_id

    timestamps()
  end

The comment also has is_active and is_active_last_updated property.

I have a business requirement where I need to get the list of posts along with active comment. And If a POST does not have any active comments. I need to get the latest active comment indicated by is_active_last_updated .

example:
POST#1

%{
  title: "This is post 1",
  id: "uuid-uuid-uuid-uuid-1",
  comments: [
    %{
      message: "This is a post 1 message",
      id: "uuid-uuid-uuid-message-1",
      is_active: true,
      is_active_last_updated: ~U[2024-03-15 15:50:18Z]
    },
    %{
      message: "This is another post 1 message which is inactive",
      id: "uuid-uuid-uuid-message-2",
      is_active: false,
      is_active_last_updated: ~U[2024-03-15 12:50:18Z]
    },
    %{
      message: "This is another post 1 message which is inactive",
      id: "uuid-uuid-uuid-message-3",
      is_active: false,
      is_active_last_updated: ~U[2024-03-15 11:50:18Z]
    }
  ]
}

POST#2

%{
  title: "This is post 2",
  id: "uuid-uuid-uuid-uuid-2",
  comments: [
    %{
      message: "This is a post 2 message which in inactive",
      id: "uuid-uuid-uuid-message-4",
      is_active: false,
      is_active_last_updated: ~U[2024-03-15 15:50:18Z]
    },
    %{
      message: "This is another post 2 message which is inactive",
      id: "uuid-uuid-uuid-message-5",
      is_active: false,
      is_active_last_updated: ~U[2024-03-15 12:50:18Z]
    },
    %{
      message: "This is another post 2 message which is inactive",
      id: "uuid-uuid-uuid-message-6",
      is_active: false,
      is_active_last_updated: ~U[2024-03-15 11:50:18Z]
    }
  ]
}

Here I need to write a query such that I get the response of

[
  %{
    title: "This is post 1",
    id: "uuid-uuid-uuid-uuid-1",
    comments: [
      %{
        message: "This is a post 1 message",
        id: "uuid-uuid-uuid-message-1",
        is_active: true,
        is_active_last_updated: ~U[2024-03-15 15:50:18Z]
      }
    ]
  },
  %{
    title: "This is post 2",
    id: "uuid-uuid-uuid-uuid-2",
    comments: [
      %{
        message: "This is a post 2 message which in inactive",
        id: "uuid-uuid-uuid-message-4",
        is_active: false,
        is_active_last_updated: ~U[2024-03-15 15:50:18Z]
      }
    ]
  }
]

Notice how only the active and latest is_active_last_updated are selected.

How can I achieve this with ecto?

Obviously, I can do a preload of comments and sort and select them manually. But I think there has to be a better way of handling such requirements. Possibly in a single dynamic query/subquery?

Any help is appreciated.
Thanks.