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.
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