Blocks of specific property, grouped by tag

Questions & Help Queries
1

I have started labeling my blocks (e.g. meeting notes) and TODO items according to related projects and added a duration property to them like so:

image
image278×185 7.79 KB

I would now like to query for all blocks, which have the duration property and group them by their label to obtain a table or bullet-listing similar to this:

block label duration
One todo item #project_1 1.6
Another todo item #project_2 0.6

(Note: A link/reference to the corresponding block in the first column, would be sufficient as well.)

Is this possible and how?

As a bonus, it would be awesome to be able to sum over all duration values of the same label (i.e. duration of all blocks labelled with #project_1 in the screenshot above). But I would already be very happy to have a grouped list-view.

1 Like
2
  • Is there a marker (TODO etc.) used in the TODO items?
    • It is not visible in the screenshot.
  • Where is the duration located?
    • same block
      • This is the typical place.
    • child-block
      • This is how it looks like in your screenshot.
  • Consider providing actual markdown instead of screenshots.
  • The duration-sum-per-label could be a separate query.
3

Thanks for replying.

Here is is a more complete Markdown example:

- ToDos
	- TODO One todo item #project_1
		- duration:: 1.6
	- ONGOING One todo item #project_1
		- duration:: 0.7
	- DONE Another todo item #project_2
		- duration:: 0.6
- Notes
	- Meeting minutes #project_3
		- duration:: 0.5
		- This is a note from the meeting.
		- This is another note from the meeting

And to answer your questions:

  • Some blocks have TODO marker (or DONE / ONGOING), but not all. As you can see in the example above I also put duration property on Meetings to track how long they took.
    But querying just the TODO/DONE/ONGOING items, would already be very good start.
  • The duration property is always a child-block of the block that I want to query for.
  • Let’s ignore the duration-sum-per-label for the moment, if it can be handled in a separate query (I can do the sum by hand, when I have the correct query).
4
  • Ensure that all your #project_n pages have a property type:: project
    • This criterion can be customized, but there has to be a way to say which tags are considered projects.
  • Then use the respective query for:
    • sorting:
      #+BEGIN_QUERY
{
 :query [:find ?name ?duration (pull ?b [*])
   :keys project duration b
   :where
     [?d :block/properties ?d-props]
     [(get ?d-props :duration) ?duration]
     [?d :block/parent ?b]
     [?b :block/refs ?p]
     [?p :block/properties ?p-props]
     [(get ?p-props :type) ?type]
     [(= ?type "project")]
     [?p :block/original-name ?name]
 ]
 :result-transform (fn [result]
   (map (fn [r]
     (update (:b r) :block/properties (fn [p]
       (assoc p "project" (:project r) "duration" (:duration r) )
     ) )
   ) (sort-by :project result))
 )
 :table-view? true
}
#+END_QUERY
    • grouping:
      #+BEGIN_QUERY
{
 :query [:find ?p (pull ?b [*])
   :keys id b
   :where
     [?d :block/properties ?d-props]
     [(get ?d-props :duration)]
     [?d :block/parent ?b]
     [?b :block/refs ?p]
     [?p :block/properties ?p-props]
     [(get ?p-props :type) ?type]
     [(= ?type "project")]
 ]
 :result-transform (fn [result]
     (map (fn [r] (:b
         (assoc-in r [:b :block/page] {:db/id (:id r) :block/name ""})
     )) result))
 :breadcrumb-show? false
 :group-by-page? true
}
#+END_QUERY
    • summing:
      #+BEGIN_QUERY
{
 :query [:find ?name (sum ?n-duration)
   :keys project duration
   :with ?b
   :where
     [?d :block/properties ?d-props]
     [(get ?d-props :duration) ?duration]
     [(* 1 ?duration) ?n-duration]
     [?d :block/parent ?b]
     [?b :block/refs ?p]
     [?p :block/properties ?p-props]
     [(get ?p-props :type) ?type]
     [(= ?type "project")]
     [?p :block/original-name ?name]
 ]
 :result-transform (fn [result] (sort-by :project result) )
 :view (fn [result] [:div
     (for [r result] [:div (str (:project r) ": " (:duration r))] )
 ])
}
#+END_QUERY
1 Like