Filtering on subquery binding with Ecto?

Questions / Help
1

I’m trying to translate the following SQL query to Ecto Query DSL:

SELECT fj.*, ft1.*, ft2.*
FROM fin_journal fj
LEFT JOIN (
  SELECT ft.*, fa.customer_id
  FROM fin_transactions ft
  LEFT JOIN fin_accounts fa ON fa.id = ft.fin_account_id
  WHERE fa.type = 'customer'
) ft1 ON ft1.fin_journal_entry_id = fj.id
LEFT JOIN (
  SELECT ft.*, fa.partner_id FROM fin_transactions ft
  LEFT JOIN fin_accounts fa ON fa.id = ft.fin_account_id
  WHERE fa.type = 'partner'
) ft2 ON ft2.fin_journal_entry_id = fj.id
WHERE ft1.customer_id = 1 OR ft2.partner_id = 2

(this is simplified version, the acual query is more complex)
As you can see, it selects all transactions of ‘customer’ accounts, all transactions of ‘partner’ accounts, joins them to common journal entry and filters entries belonging to specific customer or to specific partner. In the result each journal entry has either one (customer’s or partner’s) or both transactions.

So let’s try to write it in Ecto DSL:

ft1_query =
  from(FinTransaction, as: :fin_transaction)
  |> join(:left, [fin_transaction: ft], fa in assoc(ft, :fin_account), as: :fin_account)
  |> where([fin_account: fa], fa.type == "customer")

ft2_query =
  from(FinTransaction, as: :fin_transaction)
  |> join(:left, [fin_transaction: ft], fa in assoc(ft, :fin_account), as: :fin_account)
  |> where([fin_account: fa], fa.type == "partner")

from(FinJournal, as: fin_journal_entry)
|> join(:left, [fin_journal_entry: fje], ft1 in subquery(ft1_query), on: ft1.fin_journal_entry_id == fje.id, as: fin_transaction_1)
|> join(:left, [fin_journal_entry: fje], ft2 in subquery(ft2_query), on: ft2.fin_journal_entry_id == fje.id, as: fin_transaction_2)
|> where([fin_transaction_1: ft1, fin_transaction_2: ft2], ...)
|> preload([fin_transaction_1: ft1, fin_transaction_2: ft2], [fin_transaction_1: ft1, fin_transaction_2: ft2])

Here in the where clause we can’t reference :fin_account from subqueries to filter on their fields. Also we can’t use preloads in subqueries to add the financial accounts data to their results. If we use select in subqueries to select customer_id and partner_id, then we can’t select whole transaction struct and should select each field individually and then somehow reconstruct the struct in the parent query - this approach seems error-prone.

So is there any way this query could be implemented without questionable techniques like listing all schema fields in the query?

1 Like
2

Add the following to the subqueries.

subquery
|> select([fin_transaction: ft], map(ft, […columns]))
|> select_merge([fin_account: fa], %{partner_id: fa.partner_id})

Even with ecto having defaults for select it doesn’t remove the need to be explicit if you select from multiple bindings.

3

OK, but now subqueries return maps with transaction fields. How can I turn them to the FinTransaction struct again and put into FinJournal? It seems that Ecto’s struct, map, merge can’t build new structs from maps, neither preload can.