How can I create a time-spent-on-tasks report for a tree of tasks or single page of tasks?

Questions & Help Queries
1

I’ve spent the last few days trying to create a query (simple or advanced) that is a basic time-tracking report for a tree of tasks or single page of tasks. I’m specifically looking to replace the clocktable feature of org-mode that shows…

  • The full tree of tasks for the page or just the current task and all its children
  • Each task’s total time spent working on it
  • Parent tasks show the total time spent on all child tasks

I cannot figure out how to build a query that works across only a single task and its children or a single page. I’ve got my data on a single page currently to help with reducing complexity when querying, but nothing I’ve tried based on posts in this forum and elsewhere works thus far. I’ve basically tossed all of my previous work in favor of asking for help here.

I’m new to Logseq and am totally lost with this currently. I’ve included my data and an example of the org-mode clocktable below.

Any help would be greatly appreciated.

Additional Detail/Context:

  • I’ve got Logseq set to use Org as the file format (my preferred)
  • This is the last ‘major thing’ I need to re-create in Logseq so I can stop using emacs + org-mode
  • I use TODO, WAITING, DOING, DONE as my todo statuses/markers

What an org-mode clocktable would look like for the below data as a single page in Logseq:

#+BEGIN: clocktable :scope subtree :maxlevel 4 :indent t
#+CAPTION: Clock summary at [2024-10-18 Fri 08:01]
| Headline                                 | Time    |       |       |       |
|------------------------------------------+---------+-------+-------+-------|
| *Total time*                             | *46:16* |       |       |       |
|------------------------------------------+---------+-------+-------+-------|
| \_   borderlands                         |         |       | 33:31 |       |
| \_    main story                         |         |       |       | 20:00 |
| \_    the zombie island of dr ned        |         |       |       |  2:15 |
| \_    mad moxxis underdome riot          |         |       |       |  3:16 |
| \_    the secret armory of general knoxx |         |       |       |  5:31 |
| \_    claptraps new robot revolution     |         |       |       |  2:29 |
| \_   borderlands pre sequel              |         |       | 12:45 |       |
| \_    main story                         |         |       |       |  7:28 |
#+END:

The todo data that was used to generate the above clocktable with emacs + org-mode:

* DONE borderlands
** DONE main story
:LOGBOOK:
CLOCK: [2024-09-24 Tue 18:00]--[2024-09-24 Tue 19:00] =>  1:00
CLOCK: [2024-09-23 Mon 21:00]--[2024-09-23 Mon 23:00] =>  2:00
CLOCK: [2024-09-23 Mon 16:00]--[2024-09-23 Mon 17:00] =>  1:00
CLOCK: [2024-09-22 Sun 10:00]--[2024-09-22 Sun 18:00] =>  8:00
CLOCK: [2024-09-21 Sat 10:00]--[2024-09-21 Sat 18:00] =>  8:00
:END:
** DONE the zombie island of dr ned
:LOGBOOK:
CLOCK: [2024-09-24 Tue 21:15]--[2024-09-24 Tue 23:30] =>  2:15
:END:
** DONE mad moxxis underdome riot
:LOGBOOK:
CLOCK: [2024-09-25 Wed 16:16]--[2024-09-25 Wed 18:41] =>  2:25
CLOCK: [2024-09-25 Wed 15:17]--[2024-09-25 Wed 16:08] =>  0:51
:END:
** DONE the secret armory of general knoxx - starts at t-bone junction
:LOGBOOK:
CLOCK: [2024-09-27 Fri 21:50]--[2024-09-27 Fri 22:10] => 0:20
CLOCK: [2024-09-27 Fri 19:07]--[2024-09-27 Fri 20:03] =>  0:56
CLOCK: [2024-09-27 Fri 15:58]--[2024-09-27 Fri 18:10] =>  2:12
CLOCK: [2024-09-26 Thu 18:04]--[2024-09-26 Thu 18:21] =>  0:17
CLOCK: [2024-09-26 Thu 17:10]--[2024-09-26 Thu 17:50] =>  0:40
CLOCK: [2024-09-25 Wed 21:05]--[2024-09-25 Wed 21:57] => 0:52
CLOCK: [2024-09-25 Wed 19:31]--[2024-09-25 Wed 19:45] =>  0:14
:END:
** DONE claptraps new robot revolution - starts at tartarus station
:LOGBOOK:
CLOCK: [2024-10-13 Sun 21:14]--[2024-10-13 Sun 21:49] => 0:35
CLOCK: [2024-10-13 Sun 17:43]--[2024-10-13 Sun 18:48] =>  1:05
CLOCK: [2024-09-27 Fri 22:11]--[2024-09-27 Fri 23:00] => 0:49
:END:
* WAITING borderlands pre sequel
** WAITING main story
:LOGBOOK:
CLOCK: [2024-10-17 Thu 21:33]--[2024-10-17 Thu 23:24] => 1:51
CLOCK: [2024-10-17 Thu 14:32]--[2024-10-17 Thu 18:59] =>  4:27
CLOCK: [2024-10-15 Tue 16:27]--[2024-10-15 Tue 17:17] =>  0:50
CLOCK: [2024-10-16 Wed 20:51]--[2024-10-16 Wed 22:16] => 1:25
CLOCK: [2024-10-15 Tue 21:35]--[2024-10-15 Tue 22:50] => 1:15
CLOCK: [2024-10-14 Mon 21:06]--[2024-10-14 Mon 22:32] => 1:26
CLOCK: [2024-10-14 Mon 19:01]--[2024-10-14 Mon 19:24] =>  0:23
CLOCK: [2024-10-13 Sun 21:53]--[2024-10-13 Sun 23:01] => 1:08
:END:
2

Welcome. What you are asking for is hard to do with Logseq’s queries, but it seems doable one way or another.

  • Asking here is easy. But for actual help, should provide something tangible to talk about.
  • Since you are used in breaking tasks into subtasks, should do the same with this query:
    • Break it into smaller steps and try to solve each one of them separately.
    • Rather than asking for the whole query, ask for help with the individual steps.
    • Each one of those steps could be a relatively uncomplicated query.
    • When all those queries actually work, should combine them into a single complicated one.
3

I’ve read every forum thread in the screen shot below in their entirety (including the one you reference) and I’ve written each of the queries here based on digging through this discussion forum and official LogSeq docs.

I still cannot figure out how to tie all this info together to produce anything remotely close to the question I put forth in the original post.

image
image586×657 39.8 KB

4
  • Your provided queries:
    • deal with :block/scheduled and :block/deadline
      • in contrast, your description deals with :LOGBOOK:
    • exclude non-active tasks
      • but your description contains mostly DONE tasks
  • Therefore, can we begin from which tasks you want to query for, so as to prepare a query that simply retrieves those, doing nothing else? Which of the following tasks are relevant?
    • all the tasks
    • active tasks
    • tasks that are scheduled or have a deadline
    • tasks that contain :LOGBOOK:
    • other
5

All tasks, regardless of status, on the current page with CLOCK entries in the LOGBOOK.

6

Try this:

#+BEGIN_QUERY
{:title [:b "Tasks in current page with CLOCK entries"]
 :inputs [:query-page]
 :query [:find (pull ?b [*])
  :in $ ?page-name
  :where
   [?p :block/name ?page-name]
   [?b :block/page ?p]
   [?b :block/marker]
   [?b :block/content ?c]
   [(re-pattern "LOGBOOK:\\nCLOCK:[\\S\\s]+") ?rx]
   [(re-seq ?rx ?c)]
 ]
}
#+END_QUERY
7

The query needed a tweaked regex to pull all items properly.

If an item had a log book like:

:LOGBOOK:
State changed from...
CLOCK: stuff
:END:

the original regex wouldn’t match.

With a tweak to the regex it will pull all items with clock record in the logbook, regardless of the location of the clock record. The tweaked query:

#+BEGIN_QUERY
{:title [:b "Tasks in current page with CLOCK entries"]
 :inputs [:query-page]
 :query [:find (pull ?b [*])
  :in $ ?page-name
  :where
   [?p :block/name ?page-name]
   [?b :block/page ?p]
   [?b :block/marker]
   [?b :block/content ?c]
   [(re-pattern "LOGBOOK:\\n(.+\\n)*CLOCK:[\\S\\s]+") ?rx]
   [(re-seq ?rx ?c)]
 ]
}
#+END_QUERY
8

To add additional clarity to my latest reply…

The below screen shots are the output of the query and I’m wonder if:

  • Can the query output a table with the total clock time per row?
    • Something like
      parent tree | block title | total clock time for the block|
  • Can the query output, as a table, include the parent item tree like the list output does?
  • Can parent item show the total clock time for sub-items?
  • Show clock time regardless of the state of the marker?
    • I think this is covered by the above but wanted to be explicit

The table form of the query results with the ‘page’ column disabled

image
image982×450 25.5 KB

The list form of the query results

image
image702×600 38.5 KB

9
  • None of these things can be done by the :query itself.
  • Showing the time column can be done by adding in the content the following:
    query-properties:: [:block :clock-time]
  • Everything else needs laborious clojurescript code inside :result-transform
    • The question is whether this is worthy the effort.
      • This is the stuff for plugins or other custom code.
10

Understood re the query/clojurscript hassles.

The properties bit also worked.

One last question: How could I adjust the query to look at just the current block and all children instead of the full page?

11

To limit the results under a block of specific uuid (right-click on its bullet then Copy block ref):

  • add a recursive rule like this:
    :rules [
	[(ancestor ?a ?b)
	 [?b :block/parent ?a]
	]
	[(ancestor ?a ?b)
	 [?b :block/parent ?t]
	 (ancestor ?a ?t)
	]
]
    • add % at the end of :in , to indicate that :rules are applied, so in total either:
      • :in $ ?page-name % , if :inputs [:query-page] is used
      • :in $ % , if :inputs [:query-page] is not used (i.e. missing)
  • use the rule to replace
    • these lines:
       [?p :block/name ?page-name]
 [?b :block/page ?p]
 [?b :block/marker]
    • with these ones:
       [?root :block/uuid ?uuid]
 [(str ?uuid) ?str]
 [(= ?str "XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX")] ; use actual uuid
 [?b :block/marker]
 (ancestor ?root ?b)
      • Replace XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX with the uuid of the desired block.
12

UPDATE: applied fix

I tried updating the query with your latest and it is saying the query failed with the error Missing rules var '%' in :in. I’m not sure what’s causing the error.

The query I put together based on the latest feedback:

* DONE borderlands
:PROPERTIES:
:query-table: true
query-properties:: [:block :clock-time] 
:END:
#+BEGIN_QUERY
{:title [:b "Tasks in current block tree with CLOCK entries"]
 :inputs [:query-page]
 :query [:find (pull ?b [*])
  :in $ ?page-name %
  :where
   [?root :block/uuid ?uuid]
   [(str ?uuid) ?str]
   ;[(= ?str "XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX")] ; use actual uuid
   [(= ?str "671a39d8-9732-4a37-a78a-852ca3b8acd3")] 
   [?b :block/marker]
   (ancestor ?root ?b)
   [?b :block/content ?c]
   [(re-pattern "LOGBOOK:\\n(.+\\n)*CLOCK:[\\S\\s]+") ?rx]
   [(re-seq ?rx ?c)]
 ]
 :rules [
  [(ancestor ?a ?b)
   [?b :block/parent ?a]
  ]
  [(ancestor ?a ?b)
   [?b :block/parent ?t]
   (ancestor ?a ?t)
  ]
 ]
}
#+END_QUERY
13

Right. I have updated my answer with the %-related fix.

14

That did the trick :slight_smile:

Thank you so much, I really appreciate it.

As a final question: is there a way to flag two replies as solutions or a standard method on the forum for noting 2 replies solve the original posts question? I’d like to ensure both the original query you sent and the tweaks for just the current block are flagged as solutions to my original post.

15
  • You have already used your last/final question :wink: (in post #10)
  • Similarly, only one post can be flagged as solution, the one that directly answers the thread’s title.
    • There is also the option of updating the title.
1 Like
16

Thanks to @mentaloid there are 2 solutions to the original post:

  • For showing all clocked entries on the whole page, see here
  • For showing all clocked entries for all sub-items below the query heading, see here