Phoenix/Ecto: List all posts, include 3 latest comments’ title

Question

I have a Post schema, which has_many Comment. I want to list all posts with comments on one page (I'll use pagination separately), using single db query, with following limits:

First: I want to limit preloading to 3 latest comments per post (not all of them, as there might be hundreds potentially).

Second: I want to preload only title column from the comment, and avoid 'text' column, as the text might potentially contain too much content.

My final result would be:

Post 1 ──┬── Comment 1 Title
         │── Comment 2 Title
         └── Comment 3 Title

Post 2 ──┬── Comment 1 Title
         │── Comment 2 Title
         └── Comment 3 Title

Post 3 ──┬── Comment 1 Title
         │── Comment 2 Title
         └── Comment 3 Title

...(etc)

Whatever combination I have tried, I fail to limit number of nested comments per post (my limits always limit total number of comments, rather than on per-post basis). Also my selects fail to load title only from the comments. If anyone with experience has any inputs, it would be more than appreciated.

PS: it is already suggested above, but for more clarification, here's my model:

  schema "posts" do
    field :title, :string
    field :slug, :string
    field :active, :boolean, default: true
    has_many :comments, App.Comment
    timestamps()
  end

  schema "comments" do
    field :title, :string
    field :body, :string
    field :email, :string
    field :meta, :map
    field :active, :boolean, default: false
    belongs_to :post, App.Post
    timestamps()
  end

PPS: to be even more specific, I was wondering if it is possible to have nested limits, in same manner as nested preloads:

query = from Post, preload: [:file, :image, {:comments, [:user, :icon]}], limit: [10, {:comments: 3}]

That preload will preload nested user and icon column in comments, but limit obviously does not work for nested records.

I would probably create a database view for that and create an ecto schema for it and simply use it. — NoDisplayName
I am also thinking of trying raw query, but would love to see native Ecto way. — Encho
You could just iterate through all of the posts and do a specific preload for each post. Though, that is an N+1 query, which is unfortunate. — Justin Wood

NoDisplayName NoDisplayName · Accepted Answer · 2017-03-22T08:20:22

There is a "pseudo" version of what you could do since I don't have time to actually check the code but it will get you started.

There are alternatives so it's up to you how to go in this situation.

First you create a view somehow like this:

 create view posts_with_last_comments as
   select
     p.*,
     (select array_agg(title) from comments where post_id = p.id order by inserted_at limit 3) as last_titles
   from
     posts p

and then in your app you do the following:

query = from p in "posts_with_comments"
posts = MyApp.Repo.all(query)

Please also mind that I tried to use postgresql syntax, which may change for other DBs.

Phoenix/Ecto: List all posts, include 3 latest comments’ title

2 Answers