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.