Ecto’s fragments still feel like Elixir

@remiAugust 15th, 2022

I love that it’s so easy to write SQL queries using Elixir statements with Ecto:

campaign
|> Ecto.assoc(:participants)
|> join(:left, [participant], allocations in assoc(participant, :allocations))
|> select([_, allocations], {user, sum(allocations.amount)})
|> group_by([participant], participant.id)
|> Repo.all()

But what I also really love is that if I want to eject out and write plain SQL for a specific part, it still feels like Elixir, because everything else stays the same—it’s just a tiny fragment/1 that uses safe variable interpolation.

campaign
|> Ecto.assoc(:participants)
|> join(:left, [participant], allocations in assoc(participant, :allocations))
|> select([_, allocations], {user, fragment("? - COALESCE(SUM(?), 0)", ^campaign.amount_per_participant, allocations.amount)})
|> group_by([participant], participant.id)
|> Repo.all()

And it turns out that Ecto.Query has a coalesce/2 function 🤦‍♂️ so there’s no need for fragment in the above example. I can just write:

campaign
|> Ecto.assoc(:participants)
|> join(:left, [participant], allocations in assoc(participant, :allocations))
|> select([_, allocations, user], {user, ^campaign.amount_per_participant - coalesce(sum(allocations.amount), 0)})
|> group_by([participant], participant.id)
|> Repo.all()

💜