Query on a nested model

Questions / Help
,
1

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?

2

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

:slight_smile:

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