Advanced queries for Logseq.md

• This page is a collection of some of the Advanced queries from the [[Datalog]] channel on the [[Logseq/Discord]] server. #datalog id:: 61db13f4-75e8-4f87-ad60-3ac3479c5fc8

• Resources

  • link: The first message on the datalog channel
  • link: Logseq docs - Advanced queries
  • link: Logseq datascript schema
  • link: Logseq frontend db model
  • link: How to Graph Your Data - talk by Paula Gearon
  • link: Domain modelling with datalog - talk by Norbert Wojtowicz
  • link: Athens Research ClojureFam
  • link: Learn datalog today
  • link: Uday Verma's Blog: Datascript 101
  • link: ClojureScript Cheatsheet
  • link: Hiccup syntax
  • link: Logseq datascript
  • link: Advanced queries tutorial

• Queries

  • Query to search a textual input link:: Discord date:: [[2021-03-16]] collapsed:: true
    • #+BEGIN_QUERY {:title "Advanced query to search for the text 'Quantum mechanics'" :query [:find (pull ?b [*]) :in $ ?pattern :where [?b :block/content ?c] [(re-pattern ?pattern) ?q] [(re-find ?q ?c)]] :inputs ["Quantum mechanics"] :collapsed? true} #+END_QUERY
    • Equivalent to {{query "Quantum mechanics"}}
  • Find all TODO tasks outside of the journal pages link:: Discord date:: [[2021-03-24]] collapsed:: true
    • #+BEGIN_QUERY {:title [:h2.font-bold.opacity-70 "TODO"] :query [ :find (pull ?h [*]) :where [?h :block/marker ?marker] [(= ?marker "TODO")] ] :result-transform (fn [result] (sort-by (fn [h] (get h :block/priority "Z")) result)) :collapsed? true} #+END_QUERY
  • All TODO blocks recursively related to a tag or page link:: Discord date:: [[2021-03-27]] collapsed:: true
    • How would you query for all TODO blocks that are tagged or recursively related to ancestor block/page that is tagged with a specific tag? For example, I want all TODO tasks that are tagged as clojure or contained under blocks or pages (in any indentation depth) that are tagged clojure. That way I can see all the clojure related articles I haven't read yet, without having to tag them all explicitly. link:: Discord
    • #+BEGIN_QUERY {:title "All TODO items related to the tag 'Cowling' " :query [:find (pull ?b [*]) :where [?b :block/marker "TODO"] [?b :block/page ?p] (or [?b :block/path-ref-pages [:page/name "cowl"]] [?p :page/tags [:page/name "cowl"]]) ] :collapsed? true} #+END_QUERY
  • Scheduled items and deadlines from the past week link:: Discord date:: [[2021-04-01]] collapsed:: true
    • Hey, I try to find Schedules or Due dates that are in-between specific dates, but whenever they contain a marker (TODO, NOW, ...) they should only show if that marker is not completed. Unfortunately, I'm not familiar with Datomic or Clojure at all.. link:: Discord
    • #+BEGIN_QUERY {:title "🟠 SLIPPING deadlines (<1W)" :query [:find (pull ?block [*]) :in $ ?start ?today :where (or [?block :block/scheduled ?d] [?block :block/deadline ?d]) (not [?block :block/marker ?marker] (not [(contains? #{"NOW" "LATER" "TODO" "DOING"} ?marker)])) [(>= ?d ?start)] [(<= ?d ?today)]] :inputs [:8d :today] :collapsed? true} #+END_QUERY
  • Find all unlinked pages link:: Discord date:: [[2021-04-16]] collapsed:: true
    • query-table:: false #+BEGIN_QUERY {:title "Orphan pages" :query [:find ?name :where [?p :page/name ?name] (not [?b :block/ref-pages ?p1] [?b :block/page ?p2] (or [?p1 :page/name ?name] [?p2 :page/name ?name]))] :view (fn [result] [:div.flex.flex-col (for [page result] [:a {:href (str "/page/" page)} (clojure.string/capitalize page)])]) :collapsed? true} #+END_QUERY
  • List of currently DOING tasks link:: Discord date:: [[2021-05-30]] collapsed:: true :LOGBOOK: CLOCK: [2022-01-08 Sat 19:22:04] CLOCK: [2022-01-08 Sat 19:22:07] CLOCK: [2022-01-08 Sat 19:22:13] CLOCK: [2022-01-08 Sat 19:22:14] CLOCK: [2022-01-08 Sat 19:22:24] CLOCK: [2022-01-08 Sat 19:22:26] CLOCK: [2022-01-08 Sat 19:22:28] CLOCK: [2022-01-08 Sat 19:22:31] CLOCK: [2022-01-08 Sat 19:22:32] CLOCK: [2022-01-08 Sat 19:22:42] :END:
    • query-table:: false collapsed:: true #+BEGIN_QUERY {:title "🟢 DOING" :query [:find (pull ?b [*]) :where [?b :block/marker ?marker] [(contains? #{"NOW" "DOING"} ?marker)]] :breadcrumb-show? false :result-transform (fn [result] (sort-by (fn [h] (get h :block/priority "Z")) result)) :collapsed? true} #+END_QUERY
  • Query to show random block link:: Discord date:: [[2021-07-21]] collapsed:: true
    • #+BEGIN_QUERY {:title "Give me a random block!!!" :query [:find (pull ?b [*]) :where [?b :block/content ?content] (not [(empty? ?content)])] :result-transform (fn [result] [(rand-nth result)]) :collapsed? true} #+END_QUERY
  • Query to show a random page link:: Discord date:: [[2021-07-21]] collapsed:: true
    • #+BEGIN_QUERY {:title "Give me a random page!!!" :query [:find ?name :where [?b :block/name ?name]] :result-transform (fn [result] [(rand-nth result)]) :view (fn [result] [:div.flex.flex-col (for [page result] [:a {:href (str "/#/page/" page)} (clojure.string/capitalize page)])]) :collapsed? true} #+END_QUERY
  • Search by block uuid link:: Discord date:: [[2021-07-24]] collapsed:: true
    • #+BEGIN_QUERY {:query [:find (pull ?b [*]) :in $ ?uuid :where [?b :block/uuid ?u] [(str ?u) ?s] [(= ?uuid ?s)]] :inputs ["61d9ce9c-0822-4c1d-a9f3-78537657155b"] } #+END_QUERY
  • Query backlog TODO and DOING tasks link:: Discord date:: [[2021-07-24]] collapsed:: true
    • #+BEGIN_QUERY {:title "Backlog" :query [:find (pull ?todo [*]) :in $ ?current-page :where [?p :block/name ?current-page] [?todo :block/marker ?marker] (not [?todo :block/page ?p]) (not [_ :block/refs ?todo]) [(contains? #{"TODO" "DOING"} ?marker)] ] :inputs [:current-page] :breadcrumb-show? false :result-transform (fn [result] (sort-by (fn [h] (get h :block/priority "Z")) result)) :collapsed? true} #+END_QUERY
  • Search for a keyword within a specific set of pages link:: Discord, Discord date:: [[2021-07-24]] collapsed:: true
    • #+BEGIN_QUERY {:title "Siloed search" :query [:find (pull ?b [*]) :in $ ?keyword [?title ...] :where [?p :block/original-name ?title] [?b :block/page ?p] [?b :block/content ?c] [(clojure.string/includes? ?c ?keyword)]] :inputs ["tasks" ["Advanced queries" "Advanced queries/Examples"]] } #+END_QUERY
    • #+BEGIN_QUERY {:title "Siloed search" :query [:find (pull ?b [*]) :in $ ?keyword [?title ...] :where [(str "(?i)" ?keyword) ?matcher] [(re-pattern ?matcher) ?regex] [?p :block/original-name ?title] [?b :block/page ?p] [?b :block/content ?c] [(re-find ?regex ?c)]] :inputs ["tasks" ["Advanced queries" "Advanced queries/Examples"]] } #+END_QUERY
  • Broken references link:: Discord date:: [[2021-07-26]] collapsed:: true
    • #+BEGIN_QUERY {:title "Broken References" :query [:find (pull ?b [*]) :in $ ?matcher :where [(re-pattern ?matcher) ?regex] [?b :block/content ?c] [(re-find ?regex ?c)] [(missing? $ ?b :block/refs)]] :inputs [ "\([0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}\)"] } #+END_QUERY
  • Current NOW and DOING tasks for pages tagged with "project" link:: Discord date:: [[2021-08-24]] collapsed:: true
    • #+BEGIN_QUERY {:title "🔨 Project Now" :query [:find (pull ?h [*]) :in $ ?searchterm :where [?h :block/marker ?marker] [(contains? #{"NOW" "DOING"} ?marker)] [?p :block/properties ?a] [(get ?a :type) ?t] [(= ?searchterm ?t)] [?br :block/name ?searchterm] (or [?h :block/page ?p] [?h :block/refs ?br]) ] :inputs ["project"] :result-transform (fn [result] (sort-by (fn [h] (get h :block/priority "Z")) result)) :collapsed? false} #+END_QUERY
  • Querying TODOs from hierarchy pages link:: Discord date:: [[2021-08-06]] collapsed:: true
    • #+BEGIN_QUERY {:query [:find (pull ?b [*]) :in $ ?s :where [?b :block/marker "DONE"] [?b :block/page ?p] [?p :block/original-name ?n] [(clojure.string/starts-with? ?n ?s)]] :inputs [ "Advanced queries/" ] } #+END_QUERY
    • DONE Completed this query
  • List all the pages updated in the last 3 days collapsed:: true link:: Discord, Discord date:: [[2021-09-06]], [[2021-09-09]]
    • The time entered in the Unix time stamp (see https://www.unixtimestamp.com) which is the number of milliseconds since Jan 1st 1970. query-properties:: [:page :updated-at]
    • We may have to multiply with 1000 to get the result in milliseconds, if using the above site.
    • query-sort-by:: updated-at query-sort-desc:: true query-properties:: [:page :updated-at] #+BEGIN_QUERY {:title [:h3 "Updated in last 3 days"] :query [:find (pull ?b [*]) :in $ ?end :where [?b :block/updated-at ?v] [(- ?end 259200000 ) ?period] [(>= ?v ?period)] [(< ?v ?end)] ] ; :inputs [:start-of-today-ms] ; :inputs [:right-now-ms] :inputs [:end-of-today-ms] } #+END_QUERY
  • Query for TODO and LATER but excluding SCHEDULED tasks link:: Discord date:: [[2021-09-18]] collapsed:: true
    • #+BEGIN_QUERY {:query [:find (pull ?b [*]) :where [?b :block/marker ?m] [(contains? #{"TODO" "LATER"} ?m)] [(missing? $ ?b :block/scheduled)] [(missing? $ ?b :block/deadline)] ] } #+END_QUERY
  • Query for a task having a given block property link:: Discord date:: [[2021-09-19]] collapsed:: true
    • #+BEGIN_QUERY {:title "My TASKS (type: example)" :query [:find (pull ?b [*]) :where [?b :block/marker ?marker] [(contains? #{"DONE"} ?marker)] [?b :block/properties ?prop] [(get ?prop :type) ?type] [(= "example" ?type)] ] :collapsed? false} #+END_QUERY
    • DONE Check query with task and property. type:: example
  • List all projects (type:: project) that do not have any TODOs on their pages link:: Discord date:: [[2021-09-25]] collapsed:: true
    • #+BEGIN_QUERY {:query[:find (pull ?p [*]) :where [?b :block/page ?p] [?p :block/properties ?props] [(get ?props :type) ?type] [(= ?type "project")] (not-join [?p] [?b :block/page ?p] [?b :block/marker _]) ]} #+END_QUERY
  • List all the projects with page property "status" as [[Active]] link:: Discord date:: [[2021-9-27]] collapsed:: true
    • #+BEGIN_QUERY {:query[:find (pull ?p [*]) :where [?b :block/page ?p] [?p :block/properties ?props] [(get ?props :status) ?status] [(= ?status #{"Active"})]]} #+END_QUERY
  • Query all tasks excluding those on a page [[Journal reviews]] link:: Discord, Discord date:: [[2021-09-27]] collapsed:: true
    • #+BEGIN_QUERY {:title "📅 TESTING" :query [:find (pull ?b [*]) :where [?b :block/marker ?marker] [(missing? $ ?b :block/scheduled)] [(missing? $ ?b :block/deadline)] [(contains? #{"LATER" "TODO"} ?marker)] [?b :block/page ?page] [?page :block/original-name ?name] (not[(= ?name "Journal reviews")])] } #+END_QUERY
    • #+BEGIN_QUERY {:title "Main Tasks" :query [:find (pull ?b [*]) :where [?b :block/marker ?marker] [(missing? $ ?b :block/scheduled)] [(missing? $ ?b :block/deadline)] [(contains? #{"LATER" "TODO"} ?marker)] (not [?b :block/path-refs [:block/name "test"]]) [?b :block/page ?page] [?page :block/original-name ?name] (not[(= ?name "Journal reviews")])] :breadcrumb-show? false :result-transform (fn [result] (sort-by (fn [h] (get h :block/created-at)) result)) :collapsed? true } #+END_QUERY
  • WAITING Query all scheduled/deadlined tasks and sort them by scheduled or deadline date SCHEDULED: <2022-01-30 Sun> link:: Discord date:: [[2021-10-13]] collapsed:: true
    • #+BEGIN_QUERY {:title "🔮 Future Tasks" :query [:find (pull ?block [*]) :in $ ?start ?next :where [?block :block/marker ?marker] [(contains? #{"NOW" "LATER" "TODO" "DOING" "WAITING"} ?marker)] (or [?block :block/scheduled ?d] [?block :block/deadline ?d]) [(>= ?d ?start)] [(<= ?d ?next)]] :inputs [:today :365d-after] :breadcrumb-show? false :result-transform (fn [result] (sort-by (fn [b] (or (get b :block/scheduled) (get b :block/deadline))) result)) :collapsed? true } #+END_QUERY
  • All tasks with priority A link:: Discord date:: [[2021-10-14]] collapsed:: true
    • #+BEGIN_QUERY {:title "All todos that with priority A" :query [:find (pull ?b [*]) :where [?b :block/priority "A"]]} #+END_QUERY
  • Query snippet to grab the children blocks link:: Discord date:: [[2021-10-23]] collapsed:: true
    • Replace (pull ?h [*]) with:

    • #+BEGIN_QUERY
      (pull ?h [
          :db/id
          :block/uuid
          :block/type
          :block/left
          :block/format
          :block/title
          :block/refs
          :block/_refs
          :block/path-refs
          :block/tags
          :block/content
          :block/marker
          :block/priority
          :block/properties
          :block/body
          :block/pre-block?
          :block/scheduled
          :block/deadline
          :block/repeated?
          :block/created-at
          :block/updated-at
          :block/file
          :block/parent
          :block/unordered
          :block/heading-level
          {:block/page
          [:db/id :block/name :block/original-name :block/journal-day]}
          {:block/_parent ...}])
      

  • Query for non-task blocks with a schedule/deadline in the past collapsed:: true link:: Discord date:: [[2021-11-04]]
    • query-table:: false #+BEGIN_QUERY {:title "Old Blocks" :query [:find (pull ?b [*]) :in $ ?start :where (or [?b :block/scheduled ?d] [?b :block/deadline ?d]) [(< ?d ?start)] (not [?b :block/marker ?marker] [(contains? #{"DONE" "CANCELED"} ?marker)])] :inputs [:today] :collapsed? false} #+END_QUERY
  • Query to find tasks under a block containing the text "Follow-up" link:: Discord date:: [[2021-12-10]] collapsed:: true
    • Follow-up
      • DONE find this block
      • #+BEGIN_QUERY {:title "Find: Follow-up Tasks" :query [:find (pull ?b [*]) :where [?p :block/content "Follow-up"] [?b :block/parent ?p] [?b :block/marker ?marker] [(contains? #{"TODO" "DOING" "DONE"} ?marker)]] } #+END_QUERY
  • Query all pages that have been created in the last 2 days link:: Discord date:: [[2021-12-15]] collapsed:: true
    • query-properties:: [:page :created-at] #+BEGIN_QUERY {:title "Pages in last 2 days" :query [:find (pull ?p [*]) :in $ ?today :where [?p :block/created-at ?d] [(- ?today 172800000) ?start] [(>= ?d ?start)] [(<= ?d ?today)] ] :inputs [:right-now-ms]} #+END_QUERY
    • 172800000 is 2 days in ms.
  • Query to create a table with page and todo count link:: Discord date:: [[2021-12-17]] collapsed:: true
    • query-table:: false #+BEGIN_QUERY {:title "TODO by page" :query [:find (pull ?b [:block/marker :block/parent {:block/page [:db/id :block/name]}]) :where [?b :block/marker ?marker] [(= "TODO" ?marker)] ] :result-transform (fn [result] (map (fn [[key value]] {:page (get key :block/name) :count (count value)}) (group-by :block/page result)) ) :view (fn [rows] [:table [:thead [:tr [:th "Page"] [:th "Count"] ] ] [:tbody (for [r rows] [:tr [:td [:a {:href (str "#/page/" (get r :page))} (get r :page)] ] [:td (get r :count)] ]) ]] ) } #+END_QUERY
  • Simple query to finds all the tasks created today link:: Discord, Discord, Discord date:: [[2021-12-22]] collapsed:: true
    • {{query (and (between -0d ++0d) (task TODO))}}
    • d is used to indicate a day. 0d means 0 days or today. Think of it this way: Between -0d and ++0d
      • = between today-1 and today+1 = between yesterday and tommrow = today
    • ++1d transformed to todays date +1d transformed to tomorrow -1d transformed to yesterday
  • Query all blocks on the current page having a specific tag, say "#datalog" link:: Discord date:: [[2021-12-24]] collapsed:: true
    • #+BEGIN_QUERY { :query [ :find (pull ?b [*]) :in $ ?current-page :where [?p :block/name ?current-page] [?b :block/page ?p] [?b :block/path-refs [:block/name "datalog"]] ] :inputs [:current-page]} } #+END_QUERY
  • Query to create a table from "birthday" property of a page id:: 61db1cf4-95f0-458b-97b9-a42f02c9ce12 link:: Discord date:: [[2021-12-26]]
    • query-table:: false query-properties:: [:alias :birthday] #+BEGIN_QUERY { :query [:find (pull ?b [*]) :where [?b :block/properties ?bprops] [(get ?bprops :birthday "nil") ?bs] [(not= ?bs "nil")]] :result-transform (fn [result] (sort-by (fn [h] (get-in h [:block/properties :verjaardag])) result)) } #+END_QUERY