Two left joins on the same table gives unexpected sum

Questions / Help
1

Hi everyone,

I am fairly new to Elixir/Phoenix/Ecto (7 months). I am completely stuck on a query for hours now. I am trying to do a simple Sum() on two fields in the same table so I joined twice and that multiplies the sum by 4 for some reason. If I join once, the sum is correct, otherwise both sums are broken. (the purpose of this query is to generate a csv rows.)

from(
  game in Game,
  where: game.slug == ^slug,
  left_join: challenge in assoc(game, :challenges),
  on: challenge.status == ^"active",
  left_join: user in assoc(challenge, :user),
  left_join: activity in assoc(challenge, :activities),
  left_join: donation in assoc(challenge, :donations), # If commented out, the query functions correctly and I get back correct activity.distance sum.
  group_by: [user.firstname, user.lastname, challenge.type, challenge.distance_target, challenge.slug],
  select: [
    user.firstname,
    user.lastname,
    fragment("concat(?, '/', ?, '/', ?)", type(^base_url, :string), type(^slug, :string), challenge.slug),
    challenge.type,
    challenge.distance_target,
    fragment("round(sum(coalesce(?,0)), 1)", activity.distance), # Problem: sum is multiplied by 4 :(
    fragment("round(sum(coalesce(?,0)), 1)", donation.amount), 
  ],
  distinct: true

) |> Repo.all()

I tried also alias the join using as:

left_join: activity in assoc(challenge, :activities), as: :bar

but I get

(Ecto.Query.CompileError) unsupported :as in keyword query expression

Maybe my ecto version doesn’t have “as” yet? not sure… I am using elixir 1.7.4.

Any help would be appreciated.

Thanks!

2

Yeah you need Ecto 3.0 to get as support on a join. I think that might solve your issue.

3

No other solution? For example a fragment?

Speaking of Ecto, is it packaged with Elixir or Phoenix? (Asking in case I am forced to update.)

4

For a complex query you can define a database view and use that as the source. I do that regularly for extensive queries where fragments aren’t good enough. With the recent additions to Ecto 3 that shouldn’t be necessary nearly as often.

5

Seems that adding distinct to the fragment solves the issue.