Query on a nested model

How can I do quiery on nested associations? Suppose, I want to select all the users who left a comment with “hello” in its body somewhere.

  users = Blog.User
  |> where([user], user.id == ^user_id)
  |> join(:left, [user], cmt in assoc(user, :comments))
    # ???? where cmt.contains?("hello")

  # ...........
  |> Blog.Repo.all()

How can I do that?

Ecto tries to be a thin layer over SQL, so the question becomes “How would you do it in SQL”, the answer is via like (or ilike for case insensitive in Elixir’s case), thus: where like(cmt, "hello")


Always try to frame the queries in SQL first then it generally becomes obvious how to do it via Ecto, even if you have to fallback to fragments. :slight_smile:

1 Like