Ecto - how to select all posts that have more than N comments with a single query

Hey,

Here’s the situation. I have a Post, Comment.

# Post
...
has_many(:comments, DB.Schema.Comment)
...

# Comment
...
belongs_to(:post, Post)
...

I want to select all posts that have more than N comments with a single query.

I can’t figure out how to do it, I’ve tried :

from(p1 in query,
  join: c1 in assoc(p1, :comments),
  where:
    ^number <
      subquery(from(p2 in query, join: c2 in assoc(p2, :comments), select: count(c2.id)))
)

but it doesn’t work.

Untested code

from(
 c in Comment,
 group_by: c.post_id,
 having: avg(c.post_id) > N,
 select: c.post_id
)

Thanks for the reply, but I’ve already resolved the issue with :

from(p in query,
  join: c in assoc(p, :comments),
  group_by: p.id,
  select: p,
  having: count(c.id) > ^N
)

:partying_face: