Find :db/txInstant closest to date defined in an attribute in Datomic?

Question

Let's say I have the following Datomic schema:

; --- e1
{:db/id                 #db/id[:db.part/db]
 :db/ident              :e1/guid
 :db/unique             :db.unique/identity
 :db/valueType          :db.type/string
 :db/cardinality        :db.cardinality/one
 :db.install/_attribute :db.part/db}
{:db/id                 #db/id[:db.part/db]
 :db/ident              :e1/createdAt
 :db/valueType          :db.type/instant
 :db/cardinality        :db.cardinality/one
 :db.install/_attribute :db.part/db}
{:db/id                 #db/id[:db.part/db]
 :db/ident              :e1/e2s
 :db/valueType          :db.type/ref
 :db/cardinality        :db.cardinality/many
 :db.install/_attribute :db.part/db}
 ..

; --- e2
{:db/id                 #db/id[:db.part/db]
 :db/ident              :e2/guid
 :db/valueType          :db.type/string
 :db/cardinality        :db.cardinality/one
 :db/unique             :db.unique/identity
 :db.install/_attribute :db.part/db}
{:db/id                 #db/id[:db.part/db]
 :db/ident              :e2/startedAt
 :db/valueType          :db.type/instant
 :db/cardinality        :db.cardinality/one
 :db.install/_attribute :db.part/db}
{:db/id                 #db/id[:db.part/db]
 :db/ident              :e2/stoppedAt
 :db/valueType          :db.type/instant
 :db/cardinality        :db.cardinality/one
 :db.install/_attribute :db.part/db}
 ..

I'd like to find the :db/txInstant of the last transaction made to e1 or to any e2 associated with e1 where e1's :e1/createdAt or e2's :e2/startedAt or :e2/stoppedAt is less than or equal to a supplied date (#inst). How can I construct such a query in Datomic?

Answer 1

You can query the "history" database for the "max" transaction time meeting your defined criteria. You can also examine which attribute is changed from ?attr if you want.

(d/transact conn [{:db/id        (d/tempid :db.part/user)
                   :e1/guid      (str (d/squuid))
                   :e1/createdAt #inst "2016-10-21"
                   :e1/e2s       [{:db/id        (d/tempid :db.part/user)
                                   :e2/guid      (str (d/squuid))
                                   :e2/startedAt #inst "2016-10-23"
                                   :e2/stoppedAt #inst "2016-10-25"}]}])


(d/q '[:find (max ?inst)
       :in $ ?d
       :where
       [?e1 :e1/createdAt ?create]
       [?e1 :e1/e2s ?e2]
       [?e2 :e2/startedAt ?start]
       [?e2 :e2/stoppedAt ?stop]
       [(compare ?d ?create) ?c1]
       [(compare ?d ?start) ?c2]
       [(compare ?d ?stop) ?c3]
       (not [(pos? ?c1)] [(pos? ?c2)] [(pos? ?c3)])
       [?e1 ?attr _ ?tx]
       [?tx :db/txInstant ?inst]]
     (d/history (d/db conn))
     #inst "2016-10-24")

2016-10-24 will give you the #inst you transact your datoms. 2016-10-26 will give you nothing.

UPDATE:

Please allow me to change the schema by:

1) rename e1 and e2 to "task" where a task (e1) can have many subtasks (e2)

2) keep all timestamp in the transaction itself (i.e. createdAt, startedAt, stoppedAt all become txInstant in tx).

With this change, the query is just to find the closest (max) txInstant of e1 and/or e2 (task and its subtasks) given an instant (?tc).

(q '[:find (max ?t) .
     :in $ ?e ?tc
     :where
     (or-join [?tx]
              ;; parent task created
              [?e :task/name _ ?tx]
              ;; subtask started or stopped
              (and [?e :task/subtask ?s]
                   [?s _ _ ?tx]))
     [?tx :db/txInstant ?t]
     [(compare ?tc ?t) ?x]
     [(>= ?x 0)]]
   (d/history (db conn))
   parent-task1
   #inst "2016-10-26")

Sample scenario:

@(d/transact conn [{:db/id                 #db/id[:db.part/db]
                    :db/ident              :task/name
                    :db/valueType          :db.type/string
                    :db/cardinality        :db.cardinality/one
                    :db.install/_attribute :db.part/db}
                   {:db/id                 #db/id[:db.part/db]
                    :db/ident              :task/completed
                    :db/valueType          :db.type/boolean
                    :db/cardinality        :db.cardinality/one
                    :db.install/_attribute :db.part/db}
                   {:db/id                 #db/id[:db.part/db]
                    :db/ident              :task/subtask
                    :db/valueType          :db.type/ref
                    :db/cardinality        :db.cardinality/many
                    :db.install/_attribute :db.part/db}
                   {:db/id        #db/id[:db.part/tx]
                    :db/txInstant #inst "2016-01-01"}])

;; parent task created on 10-21
(let [p               (d/tempid :db.part/user -1)
      {tids :tempids} @(d/transact conn [{:db/id        #db/id [:db.part/tx]
                                          :db/txInstant #inst "2016-10-21"}
                                         [:db/add p :task/name "Parent Task"]])]
  (def parent-task1 (d/resolve-tempid (db conn) tids p)))

;; start a subtask on 10-23
(let [s               (d/tempid :db.part/user -2)
      {tids :tempids} @(d/transact conn [{:db/id        #db/id [:db.part/tx]
                                          :db/txInstant #inst "2016-10-23"}
                                         [:db/add s :task/name "subtask 1"]
                                         [:db/add parent-task1 :task/subtask  s]])]
  (def subtask1 (d/resolve-tempid (db conn) tids s)))

;; stop the subtask on 10-25
@(d/transact conn [{:db/id #db/id [:db.part/tx]
                    :db/txInstant #inst "2016-10-25"}
                   [:db/add subtask1 :task/completed true]])

With this, the query above will give below result:

2016-10-20 => nil 
2016-10-21 => 2016-10-21 
2016-10-22 => 2016-10-21
2016-10-23 => 2016-10-23
2016-10-24 => 2016-10-23 
2016-10-25 => 2016-10-25 
2016-10-26 => 2016-10-25