How to sum values including entities with missing attributes on Datalog/DataScript/Datomic

Question

I'm learning Datalog/DataScript/Datomic. For this I've setup a simple ledger database on DataScript to play with. By now it basically consists of a set of accounts and a list of records with the attributes :entry.record/account and :entry.record/amount. Now I'm trying to get the balance of all the accounts, by summing all the :entry.record/amount for each account. This query gives me the balance for all the accounts that have records on the ledger:

  (d/q '[:find ?account ?account-name (sum ?amount)
     :with ?record
     :in $
     :where [?account :account/name ?account-name]
            [?record :entry.record/account ?account]
            [?record :entry.record/amount ?amount]]
   @conn)

But I have some accounts that still doesn't have any record registered, and they don't appear here. I want to make a query that includes them, listed with 0 value. I've been playing with or-join and missing? to include those accounts on the query but I have no clue on how to get the amount to 0 for the accounts. For example, this query:

  (d/q '[:find ?account ?account-name (sum ?amount)
     :with ?record
     :in $
     :where [?account :account/name ?account-name]
     (or-join [?record]
              (and [?record :entry.record/account ?account]
                   [?record :entry.record/amount ?amount])
              [(missing? $ ?record :entry.record/account)])]
   @conn)

Throws an exception with the message Query for unknown vars: [?amount] since the second part of the or-join can't assign a value to ?amount.

Answer 1

Datomic's Datalog is definitely uncomfortable for this sort of aggregation; my recommendation is indeed to use or-join so as to emit a zero amount:

[:find ?account ?account-name (sum ?amount)
 :with ?sum-term
 :in $
 :where [?account :account/name ?account-name]
 (or-join [?account ?amount ?sum-term]
   (and
     [?sum-term :entry.record/account ?account]
     [?sum-term :entry.record/amount ?amount])
   (and
     [(identity ?account) ?sum-term]
     [(ground 0) ?amount]))]

See also: Datomic aggregations: counting related entities without losing results with zero-count