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")
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
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