List all posts, include 3 latest comments' title

Hello all, long time listener first time caller. :slight_smile:

I have been using Elixir/Phoenix for last few months and really got hooked. Created few schemaless apps, dealing with json reading/writing. Now in my first attempt to deal with Ecto, there are still few issues I can’t wrap my mind around. I understand the concept of preloading and definitely like the idea.

My issue is that I have Post schema, which has_many Comment. I want to list all posts on one page, but with two special requests. :slight_smile:

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

Whatever combination I have tried, I fail to limit number of nested comment 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. I am still learning and generally don’t have much experience in relational databases (that’s on my bucket list). Many thanks!

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

  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

EDIT: 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.