Getting count across 4 different tables in Ecto

Phoenix Forum Questions / Help
,
1

I’m trying to join 4 tables together. why does the following code only give me one row of the first table? For my final query, I’m trying to get the number of rows that have the same account_id from 4 different tables. There is only an association from each table to the CustodialAccount but not vice versa.

  query =
      from tli in TxLnIn,
        join: tlo in TxLnOut,
        on: tli.account_id == tlo.account_id,
        join: toi in TxOnchainIn,
        on: toi.account_id == tli.account_id,
        join: too in TxOnchainOut,
        on: too.account_id == tli.account_id,
        where: tli.account_id == ^account_id,
        select: %{
          tx_ln_in_count: count(tli.id),
          tx_ln_out_count: count(tlo.id),
          tx_on_in_count: count(toi.id),
          tx_on_out_count: count(too.id)
        }
    Repo.all(query)
2

A couple of points to note:

  1. The result of joining tables together is effectively another table that contains all the columns of the joined tables. Therefore you are really querying one table (the joined results), not four

  2. Since you are selecting an aggregate function (count) there result of the select is just one row, the aggregate row

  3. count in Postgres at least ignores NULL columns to the count expressions return the count of non-NULL columns

Hopefully that helps a little bit getting towards what you are after.

2 Likes