Thanks to Ecto.Query, we can easily add/compose where clauses
E.g.
def foo(query) do
where(query, [x], x.bar > 5)
end
But is it possible to do that with the ON clauses in a join? I don’t mean adding more joins, but dynamically adding multiple ONs to the same join. I know the keyword syntax achieves this if you only need the == operator, but what if my needs are greater?
E.g. achive this
join(query, :inner, [y], x in X, on: x.y_id == y.id and x.bar > 5)
In a more composable way
join(query, :inner, [y], x in X, on: x.y_id == y.id)
|> add_on(x.bar > 5)
You can use Ecto.Query — Ecto v3.11.2 to compose the condition separately.
For my own edification, why not just use where? I was taught long ago not put filter conditions into ON but realizing at this point it’s just something I do (or rather don’t do) without really knowing why. In searching a bit now I see they can give different results in outer joins, so that is obviously something. Otherwise maybe just a readability issue? ON x.bar > 5 does feel a little off to me but again, I’m not too sure why I feel that way and if it even matters 
Is there an advantage to writing it like this?
1 Like
Ya I thought so too, but WHERE vs ON actually lead to different results entirely which I am now running into with my use case
Here is a better explanation than what I could try to articulate
1 Like
