In Ecto, using a query function in a query with a join

Question

I have 2 models Player and Event. A player has_many events. I have an ecto query that gets the top scorers as follows:

from(p in Player,
  join: s in assoc(p, :events),
  group_by: [p.id],
  select: %{total_goals: count(s.id), player: p},
  where: s.type == "goal_scored",
  where: p.team_id == ^team_id,
  order_by: [desc: count(s.id)],
  limit: 10
)
|> Repo.all()

This works fine, but in my Event model I have a handy function to only query events of type goal_scored:

def goals(query) do
  from(e in query, where: e.type == "goal_scored")
end

Is there any way I can use this query function in my query containing the join?

Answer 1

Ecto.Query.dynamic/2 is your friend here. It’s impossible directly with the full query in Event, but you might split it that way:

def goal_scored_condition do
  dynamic([e], e.type == "goal_scored")
end

def goals(query) do
  conditions = goal_scored_condition()
  from(e in query, where: ^conditions)
end

and in Player

conditions = Event.goal_scored_condition()
from(p in Player,
  ...
  where: ^conditions,
  ...

---

In some cases, Ecto.Query.subquery/2 might be helpful as well.