Logseq advanced query for tasks table (including scheduled/deadline dates)

README.md

This is a logseq advanced query which fetches all your tasks, sorts them by (the earlier of) scheduled and deadline date if those are defined and then displays them in a table.

Here's how the query looks for the example tasks above:

example-tasks.md

• TODO Do the dishes project:: [[Chores]] SCHEDULED: <2024-09-13 Fri> DEADLINE: <2024-09-15 Sun>
• TODO Clean the bathroom project:: [[Chores]] SCHEDULED: <2024-09-14 Sat> DEADLINE: <2024-09-21 Sat>
• TODO Homework project:: [[School]] SCHEDULED: <2024-09-20 Fri> DEADLINE: <2024-09-20 Fri>

query.cljs

#+BEGIN_QUERY
{:title "BACKLOG"
:query [:find ?status (pull ?todo [*])
  :keys status task
  :where
  [?todo :block/marker ?status]
  [(contains? #{"NOW" "LATER" "DOING" "TODO" "IN-PROGRESS" "WAIT" "WAITING"} ?status)]
  ; I keep all my templates in a page called "templates" and this is how I filter out TODOS defined insde them. You can delete it if you don't have such a page
  (not [?todo :block/page [:block/name "templates"]])]
 :result-transform (fn [result] 
  (sort-by
    (min (fn [d] (get-in d [:task :block/deadline] 99999999)) (fn [d] (get-in d [:task :block/scheduled] 99999999)))
    result
  )
)
:view (fn [rows] 
  (defn dateformat [datestr]
    (let [year (subs datestr 0 4)]
      (let [month (subs datestr 4 6)]
        (let [day (subs datestr 6 8)]
          (str year "-" month "-" day)
        )
      )
    )
  )
  [:div.overflow-x-auto.query-table {:width "100%"} [:table.table-auto 
    [:thead [:tr 
      [:th.whitespace-nowrap ">"] 
      [:th.whitespace-nowrap  {:width "50%"} "Task"] 
      [:th.whitespace-nowrap "Project"] 
      [:th.whitespace-nowrap "Status"] 
      [:th.whitespace-nowrap "Scheduled"] 
      [:th.whitespace-nowrap "Deadline"]
    ]]
    [:tbody (for [r rows] [:tr 
      [:td.whitespace-nowrap [:a {:href (str "#/page/" (get-in r [:task :block/uuid]))} ">" ] ]
      [:td.whitespace-nowrap (clojure.string/replace (first (str/split-lines (get-in r [:task :block/content]))) (re-pattern "^[^ ]+ ") "")]
      [:td.whitespace-nowrap (if (not (nil? (get-in r [:task :block/properties :project]))) [:a {:href (str "#/page/" (first (get-in r [:task :block/properties :project])) )} (str "[[" (first (get-in r [:task :block/properties :project])) "]]") ]) ]
      [:td.whitespace-nowrap (str (get-in r [:task :block/marker])) ]
      [:td.whitespace-nowrap (dateformat (str (get-in r [:task :block/scheduled]))) ]
      [:td.whitespace-nowrap (dateformat (str (get-in r [:task :block/deadline]))) ]
    ]) ]
  ] ]
)
:collapsed? false}
#+END_QUERY