Queries for task management

[Siferiax]

Start here: https://docs.logseq.com/#/page/advanced%20queries

[Felipe_Alanis]

thank you very much once again!

But unfortunately, it is not working…
if i add the tag to parent block, it dissapears from the query results with all its children blocks, which is ok; but if I remove the tag from the parent, it is shown in the query results with all the child bullets regardless if the child bullets have the “#hd” tag or not…

[Siferiax]

You probably see the child bullets as part of the parent block.
They are pulled in, because you see the entirety of the block. That is to say the parent with all children etc.
Say your data is:

• parent
  • child #hd

Then the query will pull parent as part of the result set.
This is then displayed as.

• parent
  • child #hd

Because it is a direct relationship.

Actually now that I think on it.
What you could try is make it a seperate (not )
Though I feel it would stop the parent from being pulled entirely.

But I’m just doing this off the top of my head!
Just know that child blocks are pulled as part of the parent result. I think that is what is happening anyway.

[Felipe_Alanis]

It makes sense. Thanks for answering.

[grignaak]

This helped me write my first advanced query. Given a person’s page, it shows all the tasks related to that person. I used query-page so it could go on the right sidebar.

#+BEGIN_QUERY
{ :title [:h3 "👂🏻 Things to discuss"]
  :query [
    :find (pull ?b [*])
    :in $ ?here
    :where
      (page-ref ?b ?here)
      (not
        (task ?b #{"DONE" "CANCELED"}))
      (or
        (property ?b :discuss-with ?here)
        (property ?b :waiting-for ?here)
        (property ?b :follow-up ?here))]
  :inputs [:query-page]
}
#+END_QUERY

[Tiago_Pontes]

Hello.

Looking for a query equivalent to {{query (page-property type [[Thing]])}}, though adding a column with respective task counts…
Not being able to make it work because when filtering blocks through markers, the ones that have NO tasks don’t show up.

Help please – it would go great with Different ways to structure data - #63 by and_yet_it_moves .

[amanfdk]

I have pages for Project with properties.
Page properties have type=Project and status=Ongoing for Current Projects.

type:: [[Project]]
status:: Ongoing

This advanced query filters them.

#+BEGIN_QUERY
{:title "Project Pages with Status Ongoing"
 :query (and (page-property :status "Ongoing") (page-property :type "Project"))
}
#+END_QUERY

Another Variant

#+BEGIN_QUERY
{:title [:h2 "Project Pages with Status Ongoing"]
 :query [:find (pull ?p [*])
         :where
         (property ?p :type "Project")
         (property ?p :status "Ongoing")
]}
#+END_QUERY

Just shared query incase its useful for others.

[amanfdk]

I wanted to create dashboard for Tasks Pending by count. Although i was able to use Count to get Count by Types but they were not coming in Table Format.

This query groups task by marker and prints count in table format. Can be modified further for your usecase.

#+BEGIN_QUERY
{:title "✅ Count of DOING and LATER Tasks"
 :query [:find ?marker (count ?b)
         :keys type number
         :where
         [?b :block/marker ?marker]
         [(contains? #{"DOING" "LATER"} ?marker)]
         :group-by ?marker]
 :view (fn [rows] [:table
   [:thead [:tr [:th "Type"] [:th "Count"] ] ]
   [:tbody (for [r rows] [:tr
             [:td (get-in r [:type])]
             [:td (get-in r [:number])]
           ])
   ]])
}
#+END_QUERY

Result

[Siferiax]

Yeah bit of an issue with that. I’ve been trying to do the same, worked on it awhile back.
We are trying to ask the question.
From all blocks on a page, what are the tasks? Then only count those. There is no way to say, but hold up if you don’t have tasks, count 0.
It’s because we get the dataset first and then count. Instead of for example Excel does a count on the data directly.
So because we filter this data, we cannot have a contradiction.
Give me when x is true and when x is not true, would just return everything and you will end up counting all blocks.
Writing this, I’m thinking lol, maybe with a get I’ll have to explore that train of thought later.

[jpb]

Hi all, this is awesome thanks! I’ve been doing some research on datalog and logseq quries, but I haven’t been able to include restrictions on time when the query is executed. I would like to see, for instance, my home tasks everyday at 4pm only, and not before that. Is it possible to build a query that excutes its task only if the time is past 4pm, and before 6am ?

Many thanks,
JEEP

[Siferiax]

Yes, with some caveats.
The query will always run. However upon refresh it may or may not show content.

I use it to show start of day and end of day reflection. So most of the time this query shows “no matched results”. But at specific times it does.

#+BEGIN_QUERY
{:title [:h4 "🧘🏽‍♀️ Reflectie"]
 :query [:find (pull ?b [*])
  :in $ ?now ?o ?a
  :where
   [?p :block/name "reflectie"]
   [?b :block/page ?p]
   [?b :block/content ?c] ;blocks on page reflectie with certain content
   (or   ; different rules to validate against
     (and
       [(<= ?now ?o)] ; now is before 13:00
       [(clojure.string/includes? ?c "Begin van de dag")] ;block includes this content
     )
     (and
       [(<= ?a ?now)] ; now is after 17:30
       [(clojure.string/includes? ?c "Eind van de dag")] ;block includes this content
     )
   ) ;If none of this is true, show nothing
 ]
 :result-transform (fn [r] (map (fn [m] (assoc m :block/collapsed? true)) r)) ;show the block collapsed
 :inputs [:right-now-ms :today-1300 :today-1730] ;the time now, at 13:00 and 17:30
}
#+END_QUERY

You can change the conditions as you wish obviously. Hopefully this provides a good starting point.
If you need more specific help, let me know!

Ps. Let me mention that I have an extremely complex set up using the same idea.
It has 7 inputs, one is today, the rest is times. Showing different things at different times, but also based on other conditions…

[jpb]

Hi thank you for this. I’ve been playing around with the code, I get it to work, but not to show up between the appropriate hours I want it to show up. If I write “:today” in inputs the query shows results, which confirms my code works overall, but when I use your inputs it doesn’t work. Here’s something I tried:

:inputs [:right-now-ms :today-0700 :today-1700] ;display reults from 07:00 to 17:00

Also not working.

I also tried:
:inputs [:today-0700 :today-1700] ;display reults from 07:00 to 17:00.

not working !! I’ve been reading https://docs.logseq.com/#/page/advanced%20queries but I do not see what it is that I do wrong.

Again, just to be clear, here’s what I’m trying to do: “If it is between 7am and 5pm, execute this query.”

[Siferiax]

Hi, what does the rest of the query look like?
I cannot help you based on this information alone I’m afraid.
What is in :inputs corresponds with what is behind :in $

So in my example we have
:in $ ?now ?o ?a
:inputs [:right-now-ms :today-1300 :today-1730]

Meaning ?now will have the value of :right-now-ms, ?o will have the value of :today-1300 and ?a has the value of :today-1730.

These are then used together, for example here:
[(<= ?now ?o)] to determine if that is true or false. In this case is it right now (on your device) before or at 1300.

So yeah, without more details, this is all I can tell you.

[jpb]

This is the thing i’m trying to pull off… get all tasks from maison page after 16:00… I’m pulling my hair off!!

{:title [:h4 "🧘🏽‍♀️ Reflectie"]
     :query [:find (pull ?h [*])
     :in $ ?now ?a
     :where
     [?h :block/marker ?marker]
     [(contains? #{"NOW" "LATER"} ?marker)]
     [?p :block/name "maison"]
     [?b :block/page ?p]
     (or   ; different rules to validate against
       (and
         [(>= ?a ?now)] ; now is before 18:00
       )
     ) ;If none of this is true, show nothing
     ]
     :result-transform (fn [r] (map (fn [m] (assoc m :block/collapsed? true)) r)) ;show the block collapsed
     :inputs [:right-now-ms :today-1800] ;the time now, at 13:00 and 17:30
     :collapsed? true
    }

[Siferiax]

So 2 things. 1 you put the blocks on the maison page in variable ?b, but you pull ?h.
And you don’t need the and or or, because there is only 1 condition.

Here’s what it should look like:

#+BEGIN_QUERY
{:title [:h4 "🧘🏽‍♀️ Reflectie"]
 :query [:find (pull ?h [*])
  :in $ ?now ?a
  :where
   [?h :block/marker ?marker]
   [(contains? #{"NOW" "LATER"} ?marker)]
   [?p :block/name "maison"]
   [?h :block/page ?p]
   [(>= ?a ?now)] ; now is before 18:00
 ]
 :result-transform (fn [r] (map (fn [m] (assoc m :block/collapsed? true)) r)) ;show the block collapsed
 :inputs [:right-now-ms :today-1800] ;the time now and at 18:00
 :collapsed? true
}
#+END_QUERY

Ps. Don’t pull your hair out. That’s not healthy!

[Siferiax]

@Siferiax

Recursively search for tasks that are tagged or have their parent tagged with current page.

Alternative, more simple query can be found here.

FInd nested TODOs

Since it was featured in Logseq Times, I’ll just add to this post to say I’m a dumdum and there is an attribute for this, making the query A LOT simpler! This is for the query based on page reference, not the one based on parent blocks. #+BEGIN_QUERY {:title ["Query by page & alias"] :query [:find (pull ?b [*]) :in $ ?page :where [?b :block/marker "TODO"] [?p :block/name ?page] (or-join [?b ?p] [?b :block/path-refs ?p] (and [?p :block/alias ?a] …

[jpb]

Thanks! Things are working, I’m exploring other functionalities now. I understand the structure better.

[Siferiax]

I was going to come back to this point and say, sorry I haven’t managed, but then I had a random bout of inspiration…

I’m not going to say it is flawless and probably it requires better testing, so here’s me saying, try it!

#+BEGIN_QUERY
{:title [:h3 "⚡️ Lopend"]
 :query [:find ?project (count ?b) (sum ?count) ;count ?b is required to show the correct sum of ?count
   :keys project block taken ;define keys to use in the view below
   :where
     [?t :block/name "taskcount"] ;page named taskcount
     [?p :block/tags ?t] ;get all pages tagged with taskcount
     [?p :block/journal? false] ;that aren't journals
     [?p :block/original-name ?project] ;put the original page name in variable ?project
     [?b :block/page ?p] ;get blocks on the found pages
     ;Anything above this line can be edited to fit what you need to find in terms of a project name and related tasks!
     (or
       (and
         [?b :block/marker "TODO"] ;block is a task
         [(* 1 1) ?count] ;set count to 1
       )
       (and
         (not [?b :block/marker "TODO"]) ;block is not a task
         [(* 0 1) ?count] ;set count to zero
       )
     )
 ]
 ;Use of :view to display our result in a table
 :view (fn [rows] [:table 
   [:thead [:tr [:th "Project"] [:th "Tasks"] ] ]
   [:tbody (for [r rows] [:tr 
     [:td [:a {:href (str "#/page/" (clojure.string/replace (get-in r [:project]) "/" "%2F"))} (get-in r [:project])] ]
     [:td (get-in r [:taken]) ]
   ])]
 ])
}
#+END_QUERY

Let me know how it works out. Or if you need any help adjusting the query for your needs.

[hughg]

Here’s another question for you, @Siferiax

I’ve got a query for currently “DOING” tasks (which are not “#blocked”) but I’d like to pull ones mentioning “#pinned” to the top – either by sort or by grouping – and leave ones which don’t have that tag to be sorted by date and priority.

As you can see, I started pulling out the “#pinned” block in the :where clause, to do something with it, but I can’t figure out what. Or would I somehow need to pull it out in the :result-transform? Maybe I need a has-ref? function, then I can sort by its boolean result???

{:title [:h3 "Doing"]
 :query [:find (pull ?b [*])
   :where
     [?b :block/marker ?marker]
     [(contains? #{"DOING" "NOW"} ?marker)]
     [?blocked :block/name "blocked"]
     (not [?b :block/refs ?blocked])
     [?pinned :block/name "pinned"]
     ]
 :result-transform (fn [result] (sort-by (juxt
    (fn [r] (or (get-in r [:block/scheduled]) (get-in r [:block/deadline])))
    (fn [r] (get-in r [:block/priority] "Z"))
  ) result))  ; Sort first by scheduled, then deadline, then priority.
 :table-view? false
 :group-by-page? false
 :breadcrumb-show? false
 :collapsed? false
}

For now I’ll try just highlighting the pinned ones with the :view.

[Siferiax]

One option would be sorting.
See my answer here for the basics that would entail.

Grouping query results by reference

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 …

Then we would need to somehow tell the database that it needs to sort by a value that is not always present. That’s the tricky part. You need something similar to the count mechanism I proposed in my post above yours.

And so probably your best option for this is two queries.
The first query will pull those tasks that are pinned.
The second query will pull those tasks that are not pinned.
This makes the results also visually distinct and so this would be a “grouping”.

I also ran into the issue of your query not returning anything. This is because your not statement needs to include the [?blocked :block/name "blocked"] to work properly. I fixed that below as well.

So then I get something like this:

#+BEGIN_QUERY
{:title [:h3 "Pinned Doing"]
 :query [:find (pull ?b [*])
   :where
     [?b :block/marker ?marker]
     [(contains? #{"DOING" "NOW"} ?marker)]
     (not 
       [?blocked :block/name "blocked"]
       [?b :block/refs ?blocked]
     )
     [?pinned :block/name "pinned"]
     [?b :block/refs ?pinned]
     ]
 :result-transform (fn [result] (sort-by (juxt
    (fn [r] (or (get-in r [:block/scheduled]) (get-in r [:block/deadline])))
    (fn [r] (get-in r [:block/priority] "Z"))
  ) result))  ; Sort first by scheduled, then deadline, then priority.
 :table-view? false
 :group-by-page? false
 :breadcrumb-show? false
 :collapsed? false
}
#+END_QUERY

#+BEGIN_QUERY
{:title [:h3 "Doing"]
 :query [:find (pull ?b [*])
   :where
     [?b :block/marker ?marker]
     [(contains? #{"DOING" "NOW"} ?marker)]
     (not 
        [?blocked :block/name "blocked"]
        [?b :block/refs ?blocked]
      )
     (not 
       [?pinned :block/name "pinned"]
       [?b :block/refs ?pinned]
     )
     ]
 :result-transform (fn [result] (sort-by (juxt
    (fn [r] (or (get-in r [:block/scheduled]) (get-in r [:block/deadline])))
    (fn [r] (get-in r [:block/priority] "Z"))
  ) result))  ; Sort first by scheduled, then deadline, then priority.
 :table-view? false
 :group-by-page? false
 :breadcrumb-show? false
 :collapsed? false
}
#+END_QUERY