How to create independent output rows (no child) for EACH task that meets these conditions?

Questions & Help Queries
,
17

Here is my final query just in case is useful for someone.

What does this query do:

  • Search for TO-DOs with schedule/deadline for the next 7 days.
  • Search for DOINGs (does not matter if they don’t have schedule or deadline)
  • Remove #epic parent tasks (because this task is not really doable. is just for organization/tracking)
  • Show results separately on independent rows (ignoring nested structure of tasks)
  • Organize result by first DOING -and then TODO schedule/deadline.
  • add extra columns for table view (marker, schedule and deadline)
#+BEGIN_QUERY
{:title [:h3 "▶️🗓️ DOING or PLANNED for next 7 days"]
:query [:find (pull ?b [*]) ?sched ?dead ?m ; return the block and the sched and dead variables
:keys block sched dead mark ; give the find values a key for use in result-transform
 :in $ ?start ?next
 :where

; Add the criteria for which `?b` you want to find here. 
; here scheduled and deadline dates
   [?b :block/marker ?m]
   [(get-else $ ?b :block/scheduled "-") ?sched]
   [(get-else $ ?b :block/deadline "-") ?dead]

; now we find the TO-DO for the next 7 days and also merge them with the DOINGs. DOING dates does not matter. It will appear even if it does not have any time property.
(or-join [?b ?start ?next]
       (and
       
(or ; search for both shcedule or deadlines
           [?b :block/scheduled ?d]
           [?b :block/deadline ?d]
)
[(>= ?d ?start)] ; search within next 7 days
         [(<= ?d ?next)]
         [?b :block/marker "TODO"]
)
       [?b :block/marker "DOING"] ; include doings no matter the deadline or schedule
)
[?b :block/page ?p] ; remove epic tasks
(not 
 [?b :block/refs ?t]
 [?t :block/name "epic"]
)
]
 :inputs [:today :7d-after]
 :remove-block-children? false ; show blocks separately (they already met previous conditions)
 :breadcrumb-show? true
 :collapsed? false
 :result-transform (fn [res] 
   (sort-by ; show DOINGS first. Then order by schduled and deadline
     (juxt 
       (fn [r] (get r :block/marker "DOING") )
       (fn [r] (get r :block/scheduled 99999999))
       (fn [r] (get r :block/deadline 99999999))
       (fn [r] (get r :block/content))
     )
     (map (fn [m] ; make a new map based on the query result
       (update (:block m) :block/properties ; update the block properties
         (fn [u] (assoc u :scheduled (get-in m [:sched]) :deadline (get-in m [:dead]) :marker (get-in m [:mark]) ) ) ; associate the sched and dead values set in the where clause
       )
     ) res)
   )
 )
}                                     
#+END_QUERY
2 Likes