Ecto Query fetch grandchildren with condition on child

Question

What's the best way to get all comments for a specific user's posts where the post was published on a specific day?

I.e. some_user.posts.comments where post.published_on == '2016-01-01'

I'm not interested in fetching the posts as an intermediate step, just the comments.

Answer 1

You need to join with assoc(post, :comments) and then select only the comment:

from(p in Post,
  where: p.user_id == ^user.id and p.inserted_at == ^"2016-10-12T08:30:34Z",
  join: c in assoc(p, :comments),
  select: c)

iex(1)> user = Repo.get!(User, 1)
iex(2)> Repo.all from(p in Post, where: p.user_id == ^user.id and p.inserted_at == ^"2016-10-12T08:30:34Z", join: c in assoc(p, :comments), select: c)
[debug] QUERY OK source="posts" db=0.6ms
SELECT c1."id", c1."content", c1."post_id", c1."user_id", c1."inserted_at", c1."updated_at" FROM "posts" AS p0 INNER JOIN "comments" AS c1 ON c1."post_id" = p0."id" WHERE ((p0."user_id" = $1) AND (p0."inserted_at" = $2)) [1, {{2016, 10, 12}, {8, 30, 34, 0}}]

[%MyApp.Comment{__meta__: #Ecto.Schema.Metadata<:loaded, "comments">,
  content: nil, id: 1, inserted_at: #Ecto.DateTime<2016-10-12 08:30:42>,
  post: #Ecto.Association.NotLoaded<association :post is not loaded>,
  post_id: 1, updated_at: #Ecto.DateTime<2016-10-12 08:30:42>,
  user: #Ecto.Association.NotLoaded<association :user is not loaded>,
  user_id: 1},
 %MyApp.Comment{__meta__: #Ecto.Schema.Metadata<:loaded, "comments">,
  content: nil, id: 2, inserted_at: #Ecto.DateTime<2016-10-12 08:39:41>,
  post: #Ecto.Association.NotLoaded<association :post is not loaded>,
  post_id: 1, updated_at: #Ecto.DateTime<2016-10-12 08:39:41>,
  user: #Ecto.Association.NotLoaded<association :user is not loaded>,
  user_id: 1},
 %MyApp.Comment{__meta__: #Ecto.Schema.Metadata<:loaded, "comments">,
  content: nil, id: 3, inserted_at: #Ecto.DateTime<2016-10-12 08:39:42>,
  post: #Ecto.Association.NotLoaded<association :post is not loaded>,
  post_id: 1, updated_at: #Ecto.DateTime<2016-10-12 08:39:42>,
  user: #Ecto.Association.NotLoaded<association :user is not loaded>,
  user_id: 1}]

My app above doesn't have published_on : Ecto.Date so I used inserted_at : Ecto.DateTime. Just change inserted_at to published_on and change the value to a valid type that can be cast to Ecto.Date by ecto and it should work with your app.