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.