Query to get task overview in table, with date, page and owner

When making notes, I always mark actions with WAITING, TODO, NOW etc and also in the same line, the owner. I would like to make a task overview which lists all open task, their originating page, the task-type (WAITING, TODO, …) and the owner listed.

Example input:
TODO #Peter needs to do organize an event

Ideal output:

  • Table with Page | OWNER | Task | Priority | Deadline.
  • Ideally remove owner (#Peter) from task and show separate

This table query is already 80% ready but without owner. Does someone know how to add the owner? Many thanks for solving this puzzle.

#+BEGIN_QUERY
{:title “:orange_circle: SLIPPING”
:query [:find (pull ?b [*])
:in $ ?start ?today
:where
(task ?b #{“NOW” “LATER” “TODO” “DOING” “WAITING”})
(between ?b ?start ?today)
]
:inputs [:-7d :today]
:result-transform (fn [result] (sort-by (fn [h] (get h :block/created-at)) result ) )
:view (fn [rows] [:table
[:thead [:tr
[:th “Page”]
[:th “Task”]
[:th “Priority”]
[:th “Deadline”]
]]
[:tbody (for
[r rows
:let [content (str (get r :block/content))]
:let [firstline (first (str/split-lines content))] ;this will show only the first line of the task.
]
[:tr
[:td [:a {:href (str “#/page/” (clojure.string/replace (get-in r [:block/page :block/original-name]) “/” “%2F”))}
(get-in r [:block/page :block/original-name])] ] ;make a workable link to the page, even when it is in a namespace
[:td [:a {:href (str “#/page/” (get-in r [:block/uuid]))} firstline] ] ;link to the block and show the first line
[:td (get r :block/priority) ] ;get the priority from the results
[:td (get r :block/deadline)] ;get the deadline from the results
]
)]
])
:collapsed? false
}
#+END_QUERY

Have you considered putting the owner in a property (i.e. owner:: ) ?

Good point. The query I have found and copied but I have little experience myself in making these queries. Where would you add this property to display it as [:th “Owner”] in the table? Many thanks for pointing me in the right direction.

With owner as a property, change the view clause to

:view (fn [rows] [:table
[:thead [:tr
[:th "Page"]
[:th "Owner"]
[:th "Task"]
[:th "Priority"]
[:th "Deadline"]
]]
[:tbody (for
[r rows
:let [content (str (get r :block/content))]
:let [firstline (first (str/split-lines content))] ;this will show only the first line of the task.
]
[:tr
[:td [:a {:href (str “#/page/” (clojure.string/replace (get-in r [:block/page :block/original-name]) “/” “%2F”))}
(get-in r [:block/page :block/original-name])] ] ;make a workable link to the page, even when it is in a namespace
[:td (get-in r [:block/properties :owner]) ] ;get the property value for owner
[:td [:a {:href (str “#/page/” (get-in r [:block/uuid]))} firstline] ] ;link to the block and show the first line
[:td (get r :block/priority) ] ;get the priority from the results
[:td (get r :block/deadline)] ;get the deadline from the results
]
)]
])

Thanks @Siferiax, adding owner as a collumn tot the table obviously works, but how to get the owner (Peter) of this TODO into the owner-field?

Example:
TODO #@Peter - Finish the report as discussed

Thanks for helping with the last step.

That’s the step @mentaloid mentioned

So it would be:

TODO Finish the report as discussed
owner:: Peter

Or maybe

TODO #Peter - Finish the report as discussed
owner:: Peter

In queries it is really hard to “know” the reference. So when multiple pages are being referenced, which one is the owner?
Only workaround for something like that is when each owner has a way to be identified as such.

  • always starts with @
  • is in the owner or person namespace
  • page is tagged with owner (tags:: owner)

To name a few examples. We can then adjust the query to the chosen solution.

@Siferiax you are right. To have a syntax which is close to normal language and best for most users, I would favor to change all people tags to @Peter (ideally without #) or if not possible #@Peter.

The todo would then look like:
TODO Finish the report as discussed #@Peter.

How would the table query then look like and trigger on the @? Great you want to help me to solve this!

Consider Inline properties and reuse of property values, basic implementation

1 Like

I hope this will bring you joy :slight_smile:

#+BEGIN_QUERY
{:title "🟠 SLIPPING"
 :query [:find (pull ?b [*]) ?ref
  :keys block owner
  :in $ ?start ?today
  :where
   (task ?b #{"NOW" "LATER" "TODO" "DOING" "WAITING"})
   (between ?b ?start ?today)
   [?b :block/refs ?r]
   [?r :block/original-name ?ref]
   [(clojure.string/starts-with? ?ref "@")]
 ]
 :inputs [:-7d :today]
 :result-transform (fn [result] (sort-by 
   (fn [h] (get h :block/created-at)) 
   (map 
     (fn [m] (update (:block m) :block/properties 
       (fn [u] (assoc u :owner (get-in m [:owner]) ) )
     ) )
     result
   )
 ) )
 :view (fn [rows] [:table
[:thead [:tr
[:th "Page"]
[:th "Owner"]
[:th "Task"]
[:th "Priority"]
[:th "Deadline"]
]]
[:tbody (for
[r rows
:let [content (str (get r :block/content))]
:let [firstline (first (str/split-lines content))] ;this will show only the first line of the task.
:let [noprio (clojure.string/replace firstline (str "[#" (get r :block/priority) "]") "")]
:let [nomark (clojure.string/replace noprio (get r :block/marker) "")]
:let [task (clojure.string/replace nomark (str "#" (get-in r [:block/properties :owner])) "")]
:let [name (clojure.string/replace (get-in r [:block/properties :owner]) "@" "")]
]
[:tr
[:td [:a {:href (str "#/page/" (clojure.string/replace (get-in r [:block/page :block/original-name]) "/" "%2F"))}
(get-in r [:block/page :block/original-name])] ] ;make a workable link to the page, even when it is in a namespace
[:td name ] ;get the property value for owner
[:td [:a {:href (str "#/page/" (get-in r [:block/uuid]))} task] ] ;link to the block and show the first line
[:td (get r :block/priority) ] ;get the priority from the results
[:td (get r :block/deadline)] ;get the deadline from the results
]
)]
])
:collapsed? false
}
#+END_QUERY
2 Likes

Wauw, I am very impressed. This is truly perfect and hopefully not only for me usefull but also for many other Logseq users. That #@Peter shows as ‘Peter’ is also very nice. Huge compliments to you and looking at the query, I know I never could have done this.

There is one small step left, which is hopefully very simple now. The date and the task are clickable, the owner, priority and deadline not. Would it be possible to make them clickable too??

Thanks! I had fun fiddling with it and removing things from the task text.

Where do they need to go?
I’m not sure how easy it would be to accomplish the linkage though.

Great to hear you had fun making it! For the remaining data to be clickable: owner should go to #owner, priority #A, #B or #C and Deadline to the deadline date journal page. It is already great, if this also would be possible then it’s perfect.

So deadline isn’t so easy to do. Would require an even more complex query. The rest is easy enough, so I added those.

#+BEGIN_QUERY
{:title "🟠 SLIPPING"
 :query [:find (pull ?b [*]) ?ref
  :keys block owner
  :in $ ?start ?today
  :where
   (task ?b #{"NOW" "LATER" "TODO" "DOING" "WAITING"})
   (between ?b ?start ?today)
   [?b :block/refs ?r]
   [?r :block/original-name ?ref]
   [(clojure.string/starts-with? ?ref "@")]
 ]
 :inputs [:-7d :today]
 :result-transform (fn [result] (sort-by 
   (fn [h] (get h :block/created-at)) 
   (map 
     (fn [m] (update (:block m) :block/properties 
       (fn [u] (assoc u :owner (get-in m [:owner]) ) )
     ) )
     result
   )
 ) )
 :view (fn [rows] [:table
[:thead [:tr
[:th "Page"]
[:th "Owner"]
[:th "Task"]
[:th "Priority"]
[:th "Deadline"]
]]
[:tbody (for
[r rows
:let [content (str (get r :block/content))]
:let [firstline (first (str/split-lines content))] ;this will show only the first line of the task.
:let [noprio (clojure.string/replace firstline (str "[#" (get r :block/priority) "]") "")]
:let [nomark (clojure.string/replace noprio (get r :block/marker) "")]
:let [task (clojure.string/replace nomark (str "#" (get-in r [:block/properties :owner])) "")]
:let [name (clojure.string/replace (get-in r [:block/properties :owner]) "@" "")]
]
[:tr
[:td [:a {:href (str "#/page/" (clojure.string/replace (get-in r [:block/page :block/original-name]) "/" "%2F"))}
(get-in r [:block/page :block/original-name])] ] ;make a workable link to the page, even when it is in a namespace
[:td [:a {:href (str "#/page/" (clojure.string/replace (get-in r [:block/properties :owner]) "/" "%2F"))}
name] ] ;get the property value for owner and link it 
[:td [:a {:href (str "#/page/" (get-in r [:block/uuid]))} task] ] ;link to the block and show the first line
[:td [:a {:href (str "#/page/" (clojure.string/replace (get r :block/priority) "/" "%2F"))}
(get r :block/priority)] ] ;get the priority from the results and link it
[:td (get r :block/deadline)] ;get the deadline from the results
]
)]
])
:collapsed? false
}
#+END_QUERY

Great, the most important link = to the owner is also present. I had to delete changed code for the Priority in order to get it to work, as your code gives an error. It seems some brackets and get-in syntax is not correct.

What errors did you face?
It runs without problems in my own logseq.

Interesting! I receive a block error. Not understanding code too much but removing the last [:td lines solves it, but also removes the functionality

Did it work without the links? I see no problems with what you posted.

I don’t know what changed (maybe the last Logseq update), it all works now. Many thanks!

For reference of others: this code creates a table, with originating page (or Journal date), owner, task, priority and deadline. The code can also be easily changed to create separate tables for task-types WAITING, NOW, TODO etc. Huge compliments to Siferiax in writing the code for it.

1 Like