How to keep subtasks out of your TODO queries when their parent task has switched from TODO to DOING

This was driving me crazy, and I’m so pleased with the result.

The problem: every time I switched a top-level task status to DOING, all of its subtask todos would suddenly appear in my todo queries, so that dozens of really granular tasks like “sand off rust” or “research microphone cables” would be cluttering up a list of high level tasks like “buy a house” or “career change.”

Here’s the query that solves the problem. The trick was defining a recursive rule in the inputs that checks if a block is an ancestor, then using that rule to find all blocks with no ancestors with todo markers:

{:query [:find (pull ?b [*])
        :in $ %
        [?b :block/marker ?marker]
        [(contains? #{"TODO"} ?marker)]
        ;; ancestor is true when ?ancestor is an ancestor of b
        (not (ancestor ?b ?ancestor)
             [?ancestor :block/marker ?amarker]
             [(contains? #{"TODO" "NOW" "DOING"} ?amarker)])]
    [[[[ancestor ?b ?ancestor]
				[?b :block/parent ?ancestor]]
				[[ancestor ?b ?ancestor]
				[?child :block/parent ?ancestor]
				(ancestor ?b ?child)]]]
:result-transform (fn [result]
                    (sort-by (fn [b]
                               (get b :block/priority "Z")) result))
:breadcrumb-show? false

And here’s a screenshot to demonstrate the result:

Your work is super brilliant! Interestingly, my trouble is kinda a reverse of yours: I’d like to have a parent TODO task and its sub TODO tasks ALL shown in one query listing all TODO tasks, for example. Sadly, the sub TODO tasks would show in the query only when their parent task changes to some other status like DOING.

Would you help me out? Many thanks!