jumski/Advanced queries for Logseq.md

## Advanced queries for Logseq.md

      
    Raw
  

              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