CODEWITHSUNDEEP
elixirphoenix-frameworkecto
Encho
Encho

Reputation: 3

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

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.

Upvotes: 0

Views: 602

Answers (2)

NoDisplayName
NoDisplayName

Reputation: 15736

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.

Upvotes: 1

MartinElvar
MartinElvar

Reputation: 5804

Didn't test this, but i think it is possible to use a query when preloading. However i'm a bit uncertain on the syntax.

query = from Post, preload: [:file, :image, {:comments, from(c in Comment, limit: 10), [:user, :icon]}], limit: 10

Have a look at the third example here. https://hexdocs.pm/ecto/Ecto.Repo.html#c:preload/3

Upvotes: 0

Related Questions