Note: I am using MySQL.

I have two tables named Post, Comment

  schema "posts" do
    field: id, string
    field :title, :string
    field :slug, :string
    field :active, :boolean, default: true
    timestamps()
  end

  schema "comments" do
    field: id, string
    field: post_id, string
    field :title, :string
    field :body, :string
    field :email, :string
    field :meta, :map
    field :active, :boolean, default: false
    timestamps()
  end

I am using Ecto for the database queries in Elixir. Currently, I am having the issue of trying to get all the posts in the database, and the most recent comment for those posts, if available. E.g:

Post 1 ──── post1__last_comment_id 
         
Post 2 ──── post2__last_comment_id      

Post 3 ──── nil

I have tried join_left on post.id == comment.id, but that returns all the comments related to the post not just the most recent one.

I tried also grouping_by comment.post_id and get the max id, while that worked, that is not the desired solution as there is a requirement for it to be based on date rather than id of the comment.

One more thing I tried after some suggestions on StackOverflow is using a window function since I cannot use a lateral join in MySQL. I tried to write an Ecto query for that and ended up with this:

Repo.all from p in Post, as: :post,
           join: c in assoc(p, :comments),
           inner_join: latest in subquery(
            from c1 in Comment, 
			select: {c1.start_date, over(row_number(), :parent)},
			windows: [parent: [partition_by: c1.post_id]]	
           ), on: latest.id == c.id,
           preload: [comments: c]

Unfortunately, that did not work and I am receiving this MySQL error but I can’t understand how to fix it:

** (Ecto.SubQueryError) the following exception happened when compiling a subquery.

** (Ecto.QueryError) subquery/cte must select a source (t), a field (t.field) or a map, got: `{&0.start_date(), over(row_number(), :department)}` in query:

from c0 in MyProject.Comments.Comment,
  windows: [parent: [partition_by: [c0.post_id]]],
  select: {c0.start_date, over(row_number(), :parent)}

Is it possible to be done using Ecto?