Datomic search query which sorts by date

After much fiddling I managed to create a datomic search query which

(a) identifies TODOs which reference the current page
(b) sort results by date

I thought I’d share it as I was quite pleased with getting the sorting behaviour right.

However, the sorting only works for SCHEDULED tasks as opposed to deadlines. Can anyone get dates to sort irrespective of whether the TODO is SCHEDULED or a DEADLINE?

(NB the ;; shows a line which has been commented out as it didn’t seem to affect the behaviour).

#+BEGIN_QUERY
  {:title "🔎 All todos tagged using current page"
   :query [
         :find (pull ?b [*])
         :in $ ?current-page ?start ?next
         :where
         [?p :page/name ?current-page]
         [?b :block/marker ?marker]
         [?b :block/ref-pages ?p]
        ;;  [(= "TODO" ?marker)]
        [?block :block/scheduled ?d]
        [?block :block/deadline ?d]
      [(> ?d ?start)]
      [(< ?d ?next)]
         ]
   :result-transform (fn [result]
                        (sort-by (fn [d]
                                   (get d :block/scheduled) ) result))

   :inputs [:current-page :today :7d-after]}
#+END_QUERY

@Hulk

This is great. Thank you for posting this. I’ve been trying to puzzle out how to do this with no real clojure-fu so this is very helpful.

Two questions:

  1. Did you figure out how to make it do a reverse sort (ie. so dates start at most recent and descending)?
  2. Did you manage to crack how to do it for scheduled and deadline dates together and then sorted? (I just applied your function to deadlines for me.

I currently use this function to show me overdue items in my sidebar as a toggle as part of my GTD flow. Would love to enhance it with the two capabilities above. This is gold though. Thanks!

#+BEGIN_QUERY
 {:title "⚠️ OVERDUE"
      :query [:find (pull ?block [*])
          :in $ ?start ?today
          :where
           [?block :block/marker ?marker]
          (or
            [?block :block/scheduled ?d]
            [?block :block/deadline ?d])
          [(>= ?d ?start)]
          [(<   ?d ?today)]
          [(contains? #{"NOW" "LATER" "TODO" "DOING" "WAITING"} ?marker)]] 
  :inputs [:180d :today]
  :result-transform  (fn [result]
                              (sort-by  (fn [d] 
                              (get d :block/deadline) ) result ))
  :collapsed? false}
#+END_QUERY

Glad you found it useful! No I haven’t been able to find a way to distinguish between schedules and deadlines within the datomic query itself. A work around is to just create scheduled events, but to tag anything which is a deadline (e.g. [[deadline]]). A CSS trick could be used to give it a nice emoji to aid visual processing.

No I haven’t found a way to reverse sorting, but I am not sure what practical reason one would have for showing later dates first?

Ah, the reverse sorting is mostly so I could have my Today+Overdue query to have most recently deadline things first and then sort into things further away. I’m more interested in things that became overdue in terms of notifying myself rather than things that are already quite overdue (I have lots of overdue tasks… :-./ ).

So, the reverse sort would be quite helpful.