Grouping query results by reference

[choffee]

I am trying to group a bunch of TODO items by the pages they reference in the query. Basically I want a list of all TODO items grouped by context eg. @home @shops etc. as per GTD.

I’ve got the list sort of working but I can’t get the sorting to work.

{:title "List of DOING tasks sorted by linked pages"
 :query [:find (pull ?b [*])
         :where
         [?b :block/marker "DOING"]
         [?b :block/page ?page]]

 :view {:table {:header ["Block" "link"]
                        :rows ?result }}
:result-transform (fn [result]
                                  (sort-by (fn [h]
                                                      (get-in h :block/refs) result )))
:breadcrumb-show? false
:group-by-page? false}

My logic is that I can just sort by refs but do I need to grab the first (or the one that starts with “@” in my case. What does :block/refs return is it an array of strings or something more tricky to process?

I’m not sure if the sort-by section is doing anything at all. Any ideas?

[Siferiax]

So the first step to understand is the structure of the data. And in that structure what is actually returned.

Say we have this data.

TODO a task [[@home]]

This is multiple rows in the database.
Each row in the database looks like this:
[id attribute value]
In this case id will always point to the id for that block in the database. The attribute will be the different types of information stored about the block and the value the value this attribute has.

You can right click on a bullet in developer mode to show this.
For the above:

{:block/uuid #uuid "64c8e59b-d173-4524-a1a7-adc76a9667cc",
 :block/properties {},
 :block/journal? true,
 :block/left {:db/id 1592},
 :block/refs [{:db/id 4} {:db/id 1597}],
 :block/journal-day 20230801,
 :block/format :markdown,
 :block/content "TODO a task [[@home]]",
 :db/id 1594,
 :block/path-refs [{:db/id 4} {:db/id 1592} {:db/id 1597}],
 :block/parent {:db/id 1592},
 :block/unordered true, 
 :block/page {:db/id 1592}, 
 :block/marker "TODO"}

The first part is the attribute and the second the value.
You can see the attribute :db/id has a value of 1594.
So I can write this (silly) query:

#+BEGIN_QUERY
{:title "Give me my block"
 :query [:find ?b
  :where
   [1594 :block/content ?b]
 ]
}
#+END_QUERY

And that works:

So now when we look at refs, I’m sure you’ll see what the value is here.
:block/refs [{:db/id 4} {:db/id 1597}]
References to other id’s in the database.
This will not work for our sorting purposes, we need the actual data, not the id’s.
There are 2 references, because TODO is actually a page that is referenced here.
This is another reason we can’t sort on :block/refs, because it returns multiple values.

Also your query syntax isn’t quite correct, but that is another issue.

Without sorting:

With sorting:

The query:

#+BEGIN_QUERY
{:title "List of DOING tasks sorted by linked pages"
 :query [:find ?area (pull ?b [*])
  :keys area task
  :where
   [?b :block/marker ?m]
   [(= ?m "DOING")]
   [?b :block/page ?page]
   [?b :block/refs ?a]
   [?a :block/original-name ?area]
   [(!= ?area ?m)]
 ]
 :result-transform (fn [result] 
  (sort-by (fn [s] (get-in s [:block/properties :area])) < (map (fn [m] 
     (update (:task m) :block/properties 
         (fn [u] (assoc u :area (get-in m [:area]))
         ))
  ) result))
 )
 :breadcrumb-show? false
 :group-by-page? false
}
#+END_QUERY

Yes there is some trickery here to make it work.
Also no you don’t need the view. If you want a table, press the table button:

And press the settings icon to change which columns are shown:

Let me know if you need more help with this!

[Siferiax]

Small caveat, when a block has multiple references the block will show up multiple times.
One for each reference.
We can add [(clojure.string/starts-with? ?area "@")] to make sure it only takes those references that start with the @ symbol.
You can add that after the [(!= ?area ?m)] line and before the ] line.

[tanhoaian]

Thank you @Siferiax. With your help, I’ve managed to modify above queries to find incomplete tasks with estimated finish time (#@10m, #@40m, etc). However, I couldn’t change the ‘area’ column header into ‘time’ as the column will return blank. Can you please help me?

#+BEGIN_QUERY
{:title [:h3 "Tasks By Durations"]
 :query [:find ?area (pull ?b [*])
  :keys area task
  :where
   [?b :block/marker ?m]
   [(= ?m "TODO")]
   [?b :block/page ?page]
   [?b :block/refs ?a]
   [?a :block/original-name ?area]
   [(!= ?area ?m)]
   [(clojure.string/starts-with? ?area "@")]
   [(clojure.string/ends-with? ?area "m")]
   (not [?b :block/deadline])
   (not [?b :block/scheduled])
 ]
 :result-transform (fn [result] 
  (sort-by (fn [s] (get-in s [:block/properties :area])) < (map (fn [m] 
     (update (:task m) :block/properties 
         (fn [u] (assoc u :area (get-in m [:area]))
         ))
  ) result))
 )
 :breadcrumb-show? false
 :group-by-page? false
}
#+END_QUERY

Thanks in advanced. Appreciate your help.

[Siferiax]

You will need to replace both

• the key name :keys area task => :keys time task
• the occurrences of area in the result-transform
  • (get-in s [:block/properties :area])) => (get-in s [:block/properties :time]))
  • (fn [u] (assoc u :area (get-in m [:area])) => (fn [u] (assoc u :time (get-in m [:time]))

The names dictated in the :keys clause, are related to the keys used in the result-transform as :name (eg. :area or :time).
For the assoc (associate) function the same logic applies. Put (get-in m [:area]) into key with name :area. Wherein that :area from the get refers to the key defined in the combination of :find and :keys. (?area gets mapped to key :area)

I hope I made sense here

[tanhoaian]

Thank you for your help, Siferiax. It does make sense, but unfortunately the query still returns blank column. I’m not sure where it goes wrong. Can you please take a look at below query?

#+BEGIN_QUERY
{:title [:h3 "Tasks By Duration"]
 :query [:find ?area (pull ?b [*])
  :keys time task
  :where
   [?b :block/marker ?m]
   [(= ?m "TODO")]
   [?b :block/page ?page]
   [?b :block/refs ?a]
   [?a :block/original-name ?area]
   [(!= ?area ?m)]
   [(clojure.string/starts-with? ?area "@")]
   [(clojure.string/ends-with? ?area "m")]
   (not [?b :block/deadline])
   (not [?b :block/scheduled])
 ]
 :result-transform (fn [result] 
  (sort-by (fn [s] (get-in s [:block/properties :time])) < (map (fn [m] 
     (update (:task m) :block/properties 
         (fn [u] (assoc u :time (get-in m [:time]))
         ))
  ) result))
 )
 :breadcrumb-show? false
 :group-by-page? false
}
#+END_QUERY

Thanks in advanced. Much appreciated.

[Siferiax]

The query you posted works fine for me:

[tanhoaian]

I reindexed the graph & the query works now. Thank you @Siferiax