1

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Peluko
  • 35
  • 3

1 Answers1

2

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Valentin Waeselynck
  • 5,950
  • 26
  • 43
  • It works perfectly, but it's somewhat tricky. As I understand, you use `identity` to give some binding to the free-var `?sum-term`. I've tested it by also using `(group 0) ?sum-term` and it gives the same results. Is there any issue with using `group` instead of `identity`? – Peluko Jun 14 '21 at 16:04
  • @Peluko I assume you meant `(ground 0) ?sum-term` ? That seems to work fine, but I personally find it less regular semantically, which might make the code harder to read. – Valentin Waeselynck Jun 15 '21 at 17:51