Dreams in Harmony

Powered by 🌱Roam Garden

Datomic Query Examples

Instructions

Read my blog about Roam Query.

This will only work if you have Roam42 installed.

Shift click on Template/Datomic Query and start the roam/js script under Common functions. You only need to do this only once.

Choose between two methods to run a query:

simple-query: If you do not want to pass parameters to the query and you do not want any javascript post-processing, use the simple-query.

advanced-query: The advanced query provides you with full flexibility. But looks a bit more messy.

To generate the template for your query run the appropriate Roam42 SmartBlock:

Datomic simple-template

Datomic advanced-template

I have added very basic (and limited) post processing of the result.

Results are displayed in a single block using ::hiccup. This is to avoid filling your graph with unnecessary number of blocks.

Displaying Page Titles and Dates as links

At the moment these only work outside of (pull ) statements, and outside of aggregate functions.

I convert Page Titles into page links.

Designate the title filed by adding :name to the field name

?title:name

Designate the corresponding uid by:

placing the uid immediately after the ?title:name field

and adding :uid to the field name

?title:uid

I convert dates to Daily Notes links.

Designate a field to be converted by adding :date to it

?edit:date or ?create:date

Displaying nested results as a table in the table in the table...

When executing a query that includes a (pull ) statement the result will be a tree, not a table. Technically speaking the result is a JSON data structure.

I render query results according to the following logic

The top level of the result-set is displayed as rows of a table, with values as the columns.

Nested levels in the result-set alternate between being rendered in columns or rows.

At nested levels I use MAXROWS/4 to limit the number of rows to display. Even with this the final result set could reach thousands of rows.

MAXROWS is set to 40 by default. In the advanced-query you can change this number.

Execute your query by pressing the button nested under the query.

If you find this helpful 🙏 say thanks on Twitter @zsviczian. If you like it even more, ☕ Buy me a Coffee as a show of your appreciation.

Examples

Graph statistics

How many pages do I have in my graph?

[:find (count ?title)
 :where [_ :node/title ?title]]

{{CLICK TO RUN SIMPLE-QUERY:42SmartBlock:Datomic simple-query:42RemoveButton=false}}

How many paragraphs do I have?

[:find (count ?string) 
 :where [_ :block/string ?string]]

{{CLICK TO RUN SIMPLE-QUERY:42SmartBlock:Datomic simple-query:42RemoveButton=false}}

How many paragraphs do I have that are not code blocks?

Solution using the advanced-template, this solutions is fast

let q = `[:find (distinct ?b) 
          :where [?b :block/string ?string] 
                 [(clojure.string/includes? ?string "\`\`\`")]]'`;
let exclude = window.roamAlphaAPI.q(q);

let query = `[:find (count ?string)
                :in $ ?exclude
                :where [?b :block/string ?string]
                       [(!= ?b ?exclude)]]`;

let results = window.roamAlphaAPI.q(query,exclude);

return [query, results];

{{CLICK TO RUN ADVANCED-QUERY:42SmartBlock:Datomic advanced-query:42RemoveButton=false}}

Solution using the simple-template, this solution will run very slow

Depending on the size of your graph and the power of your PC, this query may run into a browser timeout!

Note: the funny business with the + sign below is only required because I am filtering out code blocks based on the 3x`, but the same is used to create a code block that contains the query. If I would repeat that in the code, it would close the code block I am using below prematurely. 

[:find (count ?s2)
 :where [?b :block/string ?string]
        [(clojure.string/includes? ?string "``"+"`")]
        [?b2 :block/string ?s2]
        [(!= ?b2 ?b)]]

{{CLICK TO RUN SIMPLE-QUERY:42SmartBlock:Datomic simple-query:42RemoveButton=false}}

Which are my longest paragraphs that are not code blocks; and on which page do I find them?

let q = `[:find (distinct ?uid)
          :where [?b :block/string ?string] 
                 [?b :block/uid ?uid]
                 [(clojure.string/includes? ?string "\`\`\`")]]'`;
let exclude = window.roamAlphaAPI.q(q);

let query = `[:find ?title:name ?title:uid ?block_uid ?size
                :where [?b :block/string ?string]
                       [?b :block/uid ?block_uid]
                       [?b :block/page ?p]
                       [?p :node/title ?title:name]
                       [?p :block/uid ?title:uid]
                       [(count ?string) ?size]]`;

var results = window.roamAlphaAPI.q(query).filter((item,index) => !exclude[0][0].includes(item[2]))
                                          .sort((a,b) => b[3]-a[3]);
return [query, results];

{{CLICK TO RUN ADVANCED-QUERY:42SmartBlock:Datomic advanced-query:42RemoveButton=false}}

All blocks with at least 100 descendants

Based on David Bieber's work

Excluding "roam/css"

let threshold = 100;
let rule =`[[(ancestor ?child ?parent) 
            [?parent :block/children ?child]]
            [(ancestor ?child ?grand_parent) 
            [?parent :block/children ?child] 
            (ancestor ?parent ?grand_parent)]]
           ]`;

let large_blocks = window.roamAlphaAPI.q(`
        [:find ?ancestor (count ?block)
        :in $ % 
        :where [?ancestor :block/string]
                [?block :block/string]
                (ancestor ?block ?ancestor)]`,rule)
     .filter((data, index) => {return data[1] >= threshold});

let query = `[:find ?title:name ?title:uid ?text ?uid ?childcount
              :in $ [[?block ?childcount] ...] [?exclude ...]
              :where [?block :block/string ?text]
                     [?block :block/uid ?uid]
                     [?block :block/page ?page]
                     [?page  :node/title ?title:name]
                     [?page  :block/uid  ?title:uid]
                     [(not= ?title:name ?exclude)]]`;

let results = window.roamAlphaAPI.q(query,large_blocks,["roam/css"]).sort((a, b) => b[4] - a[4]);

return [query, results];

{{CLICK TO RUN ADVANCED-QUERY:42SmartBlock:Datomic advanced-query:42RemoveButton=false}}

Pages with the highest number of blocks

Based on David Bieber's work

Excluding "roam/css" and "roam/js"

let query = `[:find ?title:name ?title:uid (count ?blocks)
                :in $ [?exclude]
                :where [?page :node/title ?title:name]
                       [?page :block/uid ?title:uid]
                       [?blocks :block/page ?page]
                       [(not= ?title:name ?exclude)]]`;

let results = window.roamAlphaAPI.q(query,['roam/css','roam/js']).sort((a, b) => b[2] - a[2]);

return [query, results];

{{CLICK TO RUN ADVANCED-QUERY:42SmartBlock:Datomic advanced-query:42RemoveButton=false}}

String search

List pages within the "roam/" namespace

[:find ?title:name ?title:uid ?time:date
 :where [?page :node/title ?title:name]
        [?page :block/uid ?title:uid]
        [?page :edit/time ?time:date]
        [(clojure.string/starts-with? ?title:name "roam/")]]

{{CLICK TO RUN SIMPLE-QUERY:42SmartBlock:Datomic simple-query:42RemoveButton=false}}

List pages containing "temp"

Simple, but case sensitive solution

[:find ?title:name ?title:uid ?time:date
 :where [?page :node/title ?title:name]
        [?page :block/uid ?title:uid]
        [?page :edit/time ?time:date]
        [(clojure.string/includes? ?title:name "temp")]]

{{CLICK TO RUN SIMPLE-QUERY:42SmartBlock:Datomic simple-query:42RemoveButton=false}}

Advanced, case insensitive solution

let fragment = 'temp';
let query = `[:find ?title:name ?title:uid ?time:date
              :where [?page :node/title ?title:name]
                     [?page :block/uid ?title:uid]
                     [?page :edit/time ?time:date]]`;

let results = window.roamAlphaAPI.q(query)
  .filter((item,index) => item[0].toLowerCase().indexOf(fragment) > 0)
  .sort((a,b) => a[0].localeCompare(b[0]));

return [query, results];

{{CLICK TO RUN ADVANCED-QUERY:42SmartBlock:Datomic advanced-query:42RemoveButton=false}}

Pages modified today

Based on David Bieber's work

let rule = `[[ (ancestor ?b ?a) 
             [?a :block/children ?b]] 
             [ (ancestor ?b ?a) 
             [?parent :block/children ?b] 
             (ancestor ?parent ?a)]] 
            ]`;

let query = `[
  :find ?page_title:name ?page_title:uid
  :in $ ?start_of_day %
  :where
  [?page :node/title ?page_title:name]
  [?page :block/uid ?page_title:uid]
  (ancestor ?block ?page)
  [?block :edit/time ?time]
  [(> ?time ?start_of_day)]
]`;

let results = window.roamAlphaAPI.q(query,new Date().setHours(0, 0, 0, 0),rule);

return [query, results];

{{CLICK TO RUN ADVANCED-QUERY:42SmartBlock:Datomic advanced-query:42RemoveButton=false}}

List top-level blocks on this page

Simple version

[:find ?block_string
 :where
 [?p :node/title "Datomic Query Examples"]
 [?p :block/children ?c]
 [?c :block/string ?block_string]]

{{CLICK TO RUN SIMPLE-QUERY:42SmartBlock:Datomic simple-query:42RemoveButton=false}}

Advanced version

let uid = document.querySelector("textarea.rm-block-input").id;
uid = uid.substring( uid.length -9);

let query = `[:find ?order ?block_string
                :in $ ?uid
                :where [?origin :block/uid ?uid]
                       [?origin :block/page ?page]
                       [?page :block/children ?children]
                       [?children :block/order ?order]
                       [?children :block/string ?block_string]]`;

let results = window.roamAlphaAPI.q(query,uid).sort((a,b) => a[0]-b[0]);

return [query, results];

{{CLICK TO RUN ADVANCED-QUERY:42SmartBlock:Datomic advanced-query:42RemoveButton=false}}

Understanding the Roam data structure

List attributes associated with :node/title

I found the namespace predicate here: clojure.core namespace | ClojureDocs - Community-Powered Clojure Documentation and Examples

[:find ?namespace ?attribute
 :where
  [?a :node/title]
  [?a ?attribute]
  [(namespace ?attribute) ?namespace]]

{{CLICK TO RUN SIMPLE-QUERY:42SmartBlock:Datomic simple-query:42RemoveButton=false}}

List all attributes and their namespace in Roam

Simple version

  [:find ?Namespace ?Attribute
   :where [_ ?Attribute]
  [(namespace ?Attribute) ?Namespace]]

{{CLICK TO RUN SIMPLE-QUERY:42SmartBlock:Datomic simple-query:42RemoveButton=false}}

Advanced version

Sort the results alphabetically first by Namespace, then by Attribute

let query = `[:find ?Namespace ?Attribute
              :where [_ ?Attribute]
                     [(namespace ?Attribute) ?Namespace]]`;

let results = window.roamAlphaAPI.q(query).sort((a, b) => (a[0].localeCompare(b[0])==0) ? a[1].localeCompare(b[1]) : a[0].localeCompare(b[0]));

return [query, results];

{{CLICK TO RUN ADVANCED-QUERY:42SmartBlock:Datomic advanced-query:42RemoveButton=false}}

Pull examples

Pull top level paragraphs on this page

[:find (pull ?e [*{:block/children [*]}]) 
 :where [?e :node/title "Datomic Query Examples"]]

{{CLICK TO RUN SIMPLE-QUERY:42SmartBlock:Datomic simple-query:42RemoveButton=false}}

Pull list of children for 'roam/' namespace

[:find ?title:name ?title:uid ?time:date (pull ?page [*])
 :where [?page :node/title ?title:name]
        [?page :block/uid ?title:uid]
        [?page :edit/time ?time:date]
        [(clojure.string/starts-with? ?title:name "roam/")]]

{{CLICK TO RUN SIMPLE-QUERY:42SmartBlock:Datomic simple-query:42RemoveButton=false}}

Referenced in

Datomic Query Examples