Ecto query retrieve all posts along with the most recent comment

user_10628620 0
Viewed 411

Note: I am using MySQL 5.7.

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.

I looked up how to do it, while I found few solutions for raw SQL queries, I could not find any similar things for Ecto, and could not figure out how to convert it from SQL to Ecto.

Is it possible to be done using Ecto?

ecto elixir mysql sql
2 Answers

You can use a lateral join for this and Ecto supports this.

There's an example in the Ecto documentation that handles your usecase. See here https://hexdocs.pm/ecto/Ecto.Query.html#preload/3

Below is the example in the docs adapted to what you want.

Repo.all from p in Post, as: :post,
           join: c in assoc(p, :comments),
           inner_lateral_join: latest in subquery(
             from Comment,
             where: [post_id: parent_as(:post).id],
             order_by: [{desc: :inserted_at}],
             limit: 1,
             select: [:id]
           ), on: latest.id == c.id,
           preload: [comments: c]
user_20872040

distinct should work for this purpose:

last_comment = from c in Comments, order_by: [desc: c.id], distinct: c.posts_id

q = from p in Posts,
  preload: [comments: ^last_comment]

Repo.all(q) 
|> IO.inspect
user_70412350
Related