Advanced query to get all outstanding tasks with an ancestor that contains a string

[bodhibyte]

I’m looking for all oustanding tasks in my journal that have an ancestor block that has the string content “routine” in it (case-insensitive)

#+BEGIN_QUERY
{:query [:find (pull ?b [*])
        :in $ ?s %
        :where
        [?b :block/marker ?marker]
        [(contains? #{"TODO"} ?marker)]
        ;; ancestor is true when ?ancestor is an ancestor of b
        ((ancestor ?b ?ancestor)
             [?ancestor :block/content ?str]
             [(clojure.string/includes? ?str ?s)]
            )]
:inputs
    ["routine" [[[ancestor ?b ?ancestor]
                [?b :block/parent ?ancestor]]
                [[ancestor ?b ?ancestor]
                [?child :block/parent ?ancestor]
                (ancestor ?b ?child)]]]
        }
#+END_QUERY

Thank you in advance for your help.

[Siferiax]

Had to change quite a bit. I also separated the input string and the rules for clarity.
Main points:

• check needs to happen within the rule to work
• regular expressions are needed to make it case insensitive

#+BEGIN_QUERY
{:title "Routine tasks"
 :inputs ["routine"]
 :query [:find (pull ?b [*])
  :in $ ?input %
  :where
   [?b :block/marker ?marker]
   [(contains? #{"TODO"} ?marker)]
   [(str "(?i)" ?input) ?string]
   [(re-pattern ?string) ?pattern]
   (check-routine ?b ?pattern)
 ]
 :rules [
   [(check-routine ?b ?s)
     [?b :block/parent ?ancestor]
     [?ancestor :block/content ?c]
     [(re-find ?s ?c)]
   ]
   [(check-routine ?b ?s)
     [?b :block/parent ?ancestor]
     (check-routine ?ancestor ?s)
   ]
 ]
}
#+END_QUERY

[bodhibyte]

Wow, this is amazing! Thank you so much @Siferiax! I’ve been dissecting this query to better understand it.