Help with loading associations without affecting query

Questions / Help
1

I have a query which the number of results are being affected by some joins that I can’t quite figure out.

transaction.ex

@type t :: %__MODULE__{
          ...others
          set_rate_token_address: %Ecto.Association.NotLoaded{} | Address.t() | nil,
          set_rate_token_address_hash: Hash.Address.t() | nil,
        }

@primary_key {:hash, Hash.Full, autogenerate: false}
  schema "transactions" do
    ...fields

    has_many(:token_transfers, TokenTransfer, foreign_key: :transaction_hash)

    belongs_to(
      :set_rate_token_address,
      Address,
      foreign_key: :set_rate_token_address_hash,
      references: :hash,
      type: Hash.Address
    )

    has_one(:set_rate_token, through: [:set_rate_token_address, :token])
  end

token_transfer.ex

@primary_key false
  schema "token_transfers" do
    ...fields

    belongs_to(:transaction, Transaction,
      foreign_key: :transaction_hash,
      primary_key: true,
      references: :hash,
      type: Hash.Full
    )

    has_one(:token, through: [:token_contract_address, :token])
  end

Here is the current query:

query =
      from(
        t in Transaction,
        inner_join: b in assoc(t, :block),
        left_join: sr_tkn in assoc(t, :set_rate_token),
        on: t.set_rate_token_address_hash == sr_tkn.contract_address_hash,
        left_join: tt in assoc(t, :token_transfers),
        on: t.hash == tt.transaction_hash,
        left_join: tkn in assoc(tt, :token),
        preload: [token_transfers: {tt, token: tkn}, set_rate_token: sr_tkn],
        order_by: [{^options.order_by_direction, t.block_number}, asc: tt.log_index],
        limit: ^options.page_size,
        offset: ^offset(options),
        select:
          merge(map(t, ^@transaction_fields), %{
            block_timestamp: b.timestamp,
            confirmations: fragment("? - ?", ^max_block_number, t.block_number)
          })
      )
query
    |> where_address_match(address_hash, options)
    |> where_start_block_match(options)
    |> where_end_block_match(options)
    |> where_start_timestamp_match(options)
    |> where_end_timestamp_match(options)
    |> Repo.all()

The query is on the Transactions table, but with the current query if I set a paged amount (pagination) it does the paging on the TokenTransfers. e.g. If I query 10 transactions, and each transaction has 2 token_transfers, then I am only receiving 5 transactions back.

I would like to join the token_transfers table to the transactions table without it affecting the transactions query.

I have tried changing left_join: to join: but then it doesn’t return any results.

left_join: tt in assoc(t, :token_transfers),
left_join: tkn in assoc(tt, :token),

Any help is appreciated!

2

You cannot do that. If you join a table with more than one result per row in the base table offset based pagination doesn’t work. Either use a different query to load the joined data afterwards or join e.g. a subquery, which already has all the needed data combined into one row.

2 Likes
3

Thanks for the explanation. I will try this out!

4

I’ve added the subqueries, but not quite sure how to assign the subquery result into a specific Transaction field.

query =
      from(
        t in Transaction,
        inner_join: b in assoc(t, :block),
        order_by: [{^options.order_by_direction, t.block_number}],
        limit: ^options.page_size,
        offset: ^offset(options),
        select:
          merge(map(t, ^@transaction_fields), %{
            block_timestamp: b.timestamp,
            confirmations: fragment("? - ?", ^max_block_number, t.block_number)
          })
      )
    query =
      from(
        tkn in Token,
        join: t in subquery(query),
        on: tkn.contract_address_hash == t.set_rate_token_address_hash,
        select_merge: %{
          set_rate_token: %{
            contract_address: tkn.contract_address_hash,
            name: tkn.name,
            symbol: tkn.symbol,
            decimals: tkn.decimals,
            total_supply: tkn.total_supply
          }
        }
      )
    query =
      from(
        tt in TokenTransfer,
        join: t in subquery(query),
        on: tt.token_contract_address_hash == ^address_hash
          or tt.to_address_hash == ^address_hash
          or tt.from_address_hash == ^address_hash,
        order_by: [asc: tt.log_index],
        preload: [:token]
      )

This complains that set_rate_token: maps, lists, tuples and sources are not allowed as map values in subquery.