Sum of two left_joins returns doubled values in first table

Have you tried querying your database with plain sql to see what tables are actually returned? It might be due to how joins work, here’s an example (both inflows and outflows are duplicated):

asd@/tmp:test> select * from account;
+------+--------+
| id   | name   |
|------+--------|
| 1    | test   |
+------+--------+
SELECT 1
Time: 0.007s

asd@/tmp:test> select * from  transaction;
+------+---------+----------+--------------+
| id   | type    | amount   | account_id   |
|------+---------+----------+--------------|
| 1    | inflow  | 1000     | 1            |
| 2    | inflow  | 1000     | 1            |
| 4    | outflow | 500      | 1            |
| 3    | outflow | 1500     | 1            |
+------+---------+----------+--------------+
SELECT 4
Time: 0.009s

asd@/tmp:test> select
                  a0.id,
                  sum(t0.amount),
                  sum(t1.amount)
               from account a0
               left outer join transaction t0
                  on t0.account_id = a0.id
                  and t0.type = 'inflow'
               left outer join transaction t1
                  on t1.account_id = a0.id
                  and t1.type = 'outflow'
               group by a0.id;
+------+-------+-------+
| id   | sum   | sum   |
|------+-------+-------|
| 1    | 4000  | 4000  |
+------+-------+-------+
SELECT 1
Time: 0.011s

Try running your query without group by and sum to see what data they actually operate on:

asd@/tmp:test> select
                  a0.id,
                  t0.amount,
                  t1.amount
               from account a0
               left outer join transaction t0
                  on t0.account_id = a0.id
                  and t0.type = 'inflow'
               left outer join transaction t1
                  on t1.account_id = a0.id
                  and t1.type = 'outflow';
+------+----------+----------+
| id   | amount   | amount   |
|------+----------+----------|
| 1    | 1000     | 1500     |
| 1    | 1000     | 500      |
| 1    | 1000     | 1500     |
| 1    | 1000     | 500      |
+------+----------+----------+
SELECT 4
Time: 0.012s

Ecto does deduplicate the values returned in joins besides if you use streaming.

Ecto does deduplicate the values returned in joins besides if you use streaming.

Can you provide a quick example? I’ve never noticed it does that.

I think I’ve actually encountered several issues just on this forum where people had similar problems to OP due to ecto returning exactly what’s in the joined tables – duplicates.

What you are trying to accomplish is usually done with a UNION in SQL, not JOIN. Roughly:

SELECT account_id, amount as inflow, NULL as outflow FROM transaction WHERE type = 'inflow' AND account_id = ACCOUNT_ID

1, 1000, NULL
1, 1000, NULL

SELECT account_id, NULL as inflow, amount as outflow FROM transaction WHERE type = 'outflow' AND account_id = ACCOUNT_ID

1, NULL,  500
1, NULL, 1500

(
  SELECT account_id, amount as inflow, NULL as outflow FROM transaction WHERE type = 'inflow' AND account_id = ACCOUNT_ID
) UNION ALL (
  SELECT account_id, NULL as inflow, amount as outflow FROM transaction WHERE type = 'outflow' AND account_id = ACCOUNT_ID
)

1, 1000, NULL
1, 1000, NULL
1, NULL,  500
1, NULL, 1500

SELECT account_id, SUM(inflow), SUM(outflow) FROM (
  SELECT account_id, amount as inflow, NULL as outflow FROM transaction WHERE type = 'inflow' AND account_id = ACCOUNT_ID
) UNION ALL (
  SELECT account_id, NULL as inflow, amount as outflow FROM transaction WHERE type = 'outflow' AND account_id = ACCOUNT_ID
)
 GROUP BY account_id

1, 2000, 2000

Ecto doesn’t support unions so the easiest alternative is two separate queries - one for inflow another for outflow.

It might not do it for “sum” operations (that part I missed), but if you’re loading assocations you don’t suddenly get duplicated posts for a user, just because you happened to join comments as well.

Sort of. If you’re doing a preload with a join, the rows come out of the database with duplicates, and then Ecto de-duplicates them. There are plenty of situations where Ecto doesn’t de-duplicate stuff, ie:

iex(2)> length Repo.all from u in User
401
iex(3)> length Repo.all from u in User, join: m in assoc(u, :memberships)
674

Will return multiple copies of the same user if they have have more than one membership. This is a good thing really, it provides a nice SQL like experience.

In a way though the OP’s question is a whole other scenario. The duplication happens at the SQL level, and so does the aggregation, so there isn’t even an opportunity for Ecto to deduplicate even if it wanted to.

Associations are purely Ecto functionality so it is free to do whatever it wants. JOINs on the other hand have to follow established convention - especially as it is the persistence engine that implements it.

FYI: You can use Ecto.Multi to pass multiple statements to Ecto at once (which would make it possible to fake a union). Example:

alias MusicDB.{Repo,Album,Track}
import Ecto.Query
alias Ecto.Multi

duration_query = fn (album_id) ->
  from(a in Album, [
    join: t in Track, on: a.id == t.album_id,
    where: a.id == ^album_id,
    group_by: a.id,
    select: {a.id, sum(t.duration)}
  ])
end

combine_result = fn
  (results, _, value) when results == %{} ->
    [value]
  (results, key, value) ->
    others = Map.get(results, key)
    [value|others]
end

album_duration = fn (album_id, key) ->
  fn results ->
    result =
      album_id
      |> duration_query.()
      |> Repo.one()

    {:ok, combine_result.(results, key, result)}
  end
end

make_multi = fn (first_id, second_id) ->
  Multi.new()
  |> Multi.run(:first, album_duration.(first_id, nil))
  |> Multi.run(:second, album_duration.(second_id, :first))
end

snd = fn {_,y} -> y end
demo = fn artist_id, first_id, second_id ->
  [second_duration, first_duration] =
    make_multi.(first_id, second_id)
    |> Repo.transaction()  # {:ok, %{first: [{3, 3456}], second: [{4, 2540}, {3, 3456}]}}
    |> snd.()              # %{first: [{3, 3456}], second: [{4, 2540}, {3, 3456}]}
    |> Map.get(:second)    # [{4, 2540}, {3, 3456}]
    |> Enum.map(snd)       # [2540, 3456]

  {artist_id, first_duration, second_duration}
end

demo.(2,3,4)

iex(10)> demo.(2,3,4)

12:20:04.512 [debug] QUERY OK db=0.1ms
begin []
 
12:20:04.522 [debug] QUERY OK source="albums" db=2.3ms
SELECT a0."id", sum(t1."duration") FROM "albums" AS a0 INNER JOIN "tracks" AS t1 ON a0."id" = t1."album_id" WHERE (a0."id" = $1) GROUP BY a0."id" [3]
 
12:20:04.524 [debug] QUERY OK source="albums" db=0.3ms
SELECT a0."id", sum(t1."duration") FROM "albums" AS a0 INNER JOIN "tracks" AS t1 ON a0."id" = t1."album_id" WHERE (a0."id" = $1) GROUP BY a0."id" [4]
 
12:20:04.525 [debug] QUERY OK db=0.2ms
commit []
{2, 3456, 2540} 
iex(11)> 

The ecto multi looks interesting! How would that translate to the query in the OP? Do I try to run one query for inflow, one for outflow?

I’d start with something like:

def query(id, current_user, type) do
  from(account in App.Account, [
    left_join: t in App.Transaction,
      on: t.account_id == account.id
        and t.user_id == ^current_user.id
        and t.deleted == false
        and t.type == ^type,
     where: account.id == ^id,
     group_by: account.id,
     select: {account, sum(t.amount)}
  ])
end

run it once with "inflow" and then again with "outflow" - and then pull the combined results together.