Grouping query results by reference

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?

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:
image

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:
image

With sorting:
image

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!

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.