How to find entities with missing values ('left join')

Users
1

I have this query:

{:find  [(pull pos [*])
              (pull hold [*])
              (pull tpattern [*])]
               :where [[pos :position/account-id account-id]
                       [pos :position/holding-id hold]
                       [pos :position/trade-pattern-id tpattern]
                       [hold :holding/instrument-name instr-nm]
                       [tpattern :trade-pattern/name tp-nm]]
                :in    [account-id]}

This works fine if all 3 entities are present. However :position/holding-id and :position/trade-pattern-id are optional, so in the above query if that attribute is missing then the associated pos won’t be part of the result.

In SQL speak, I want to be able to left join hold and tpattern in the above query. This is similar to this question on Stack Overflow about Datomic Find entities with missing attributes in Datomic - Stack Overflow

2

Okay, after looking at the query unit test code, I managed to achieve a “left join” using get-attr:

(xt/q (xt/db db)
        '{:find  [(pull pos [*])
                  (pull hold [*])
                  (pull tpattern [*])]
          :where [[pos :position/id]
                  [(get-attr pos :position/holding-id nil) [hold ...]]
                  [(get-attr pos :position/trade-pattern-id nil) [tpattern ...]]]})
3 Likes