Advanced Query for NOW or due today/overdue

Questions & Help
1

Hello, everyone.

Below query works to list all of my tasks that are scheduled for today or earlier, regardless of NOW or LATER, sorted by scheduled date.

#+BEGIN_QUERY
{ :query [:find (pull ?b [*])
     :in $ ?duetoday
     :where
     [?b :block/marker ?m]
     [(contains? #{"NOW" "LATER"} ?m)]
     [?b :block/scheduled ?d]
     [(<= ?d ?duetoday)]]
:inputs [:today]
:result-transform (fn [result]
     (sort-by (fn [h]
             (get-in h [:block/scheduled])) result))
:breadcrumb-show? false}
#+END_QUERY

I’d like to add to this query so that it also pulls any tasks that are NOW, no matter what scheduled date they have (or even if they don’t have one).

In other words, “Give me a list that combines every open task scheduled for today or earlier (regardless workflow status) and all tasks with the workflow status of NOW (regardless of scheduling, even if it has no scheduling), ordered by schedule date.”

Every arrangement of “and” or “or” that I use, though, just brings up no results.

Any hints?

2

The following seems to work if I use literal value for today. I’m not sure why that is though

#+BEGIN_QUERY
{ :query [:find (pull ?b [*])
     :in $ ?duetoday
     :where
     (or-join
      [?b ?m]
      (and
        [?b :block/marker ?m]
        [(contains? #{"NOW" "LATER"} ?m)]
        [?b :block/scheduled ?d]
       ;[(<= ?d ?duetoday)]
       [(<= ?d 20220117)])
     (and [?b :block/marker ?m]
            [(contains? #{"NOW"} ?m)]))
]
:inputs [:today]
:result-transform (fn [result]
     (sort-by (fn [h]
             (get-in h [:block/scheduled])) result))
:breadcrumb-show? false}
#+END_QUERY
3

That’s odd. Does that mean I’d be locked into updating the query every day, or does duetoday work as it should as well?

4

Does this do what you wanted? (yay, a working or-query, one of the few I’ve produced so far ^^")

#+BEGIN_QUERY
{ :query [:find (pull ?b [*])
    :in $ ?today
    :where
      [?b :block/marker ?m]
      [?b :block/scheduled ?d] 
      (or 
        (and
          [(contains? #{"LATER"} ?m)]
          [(<= ?d ?today)])
        [(contains? #{"NOW"} ?m)])]
  :inputs [:today]
  :result-transform (fn [result]
    (sort-by (fn [h]
      (get-in h [:block/scheduled])) result))
  :breadcrumb-show? false}
#+END_QUERY
5

It’s close. It doesn’t include a “NOW” task with no scheduled date. “[?b :block/scheduled ?d]” comes up empty on those, so those fail to meet the criteria of the query.

I keep playing around with it to see if I can get that resolved, but I keep coming up with 0 results. For example, this is a small tweak on yours (just moves that one statement down) and looks ot me like it should work, but it comes up empty.

#+BEGIN_QUERY
{ :query [:find (pull ?b [*])
    :in $ ?today
    :where
      [?b :block/marker ?m]
       (or 
        (and
          [?b :block/scheduled ?d] 
          [(<= ?d ?today)])
        [(contains? #{"NOW"} ?m)])]
  :inputs [:today]
  :result-transform (fn [result]
    (sort-by (fn [h]
      (get-in h [:block/scheduled])) result))
  :breadcrumb-show? false}
#+END_QUERY

This feels like it should work as saying: get me everything that has a scheduled date and that date is today or earlier, OR everything with a NOW state.

6

What about this one?

#+BEGIN_QUERY
{ :query [:find (pull ?b [*])
  :in $ ?today ?tomorrow
  :where
    [?b :block/marker ?m]
    [(get-else $ ?b :block/scheduled ?tomorrow) ?d]
    (or 
      [(= "NOW" ?m)]
      [(<= ?d ?today)])]
:inputs [:today :1d-after]
:result-transform (fn [result]
  (sort-by (fn [h]
    (get-in h [:block/scheduled])) result))
:breadcrumb-show? false}
#+END_QUERY

What went wrong in your try:

You defined ?d in the same and that you wanted to use it in. I guess, that the clauses inside the and are separated (their variables are only unified after both are evaluated).

What happens in my possible solution

the get-else function takes

  1. a database ($)
  2. an entity identifier (?b)
  3. an attribute (:block/scheduled)
  4. a default value (?tomorrow)

and returns the entities attribute-value, or the default value, if it fails.
So [(get-else $ ?b :block/scheduled ?tomorrow) ?d] unifies ?d with either the scheduled date, or if there is none, with ?tomorrow.

Afterwards we can obviously check for either marker being “NOW” or schedule date being at most ?today (which, in particular, ?tomorrow is not).

For further reference you can also check Datomic Queries and Rules | Datomic (the whole page)

To improve

I only used the tomorrow’s date as a default value, because it’s bigger than today’s (that way stuff that isn’t a “NOW” and is not scheduled, gets a value that makes the or-evaluation false).
Maybe it would be better to use some “Maximum date” value, but I don’t know whether that exists.

2 Likes
7

Getting much closer! Thank you for this, and for all of the explanation!

How would I filter out tasks that have a “DONE” status?

8

add (not [(contains? #{"DONE" "CANCELED"} ?m)]) (for example right below [?b :block/marker ?m])
I love to cancel some tasks if I see they became irrelevant. Feel free to remove the “CANCELED” from the set, if you don’t use it.

9

This is now perfect! Thank you!

10

How would I modify this so it also included deadlines from today or earlier (in addition to scheduled)?

11

To be honest, for some strange reason the query does not longer do what it should do.
On my system it ignores whether the task is scheduled or whether it has a deadline. Probably there is something wrong with get-else.
But aside from that (maybe I will debug the rest of the query, maybe someone else will), it should be

#+BEGIN_QUERY
{ :query [:find (pull ?b [*])
  :in $ ?today ?tomorrow
  :where
    [?b :block/marker ?m]
    (not [(contains? #{"DONE", "CANCELED"} ?m)])
    [(get-else $ ?b :block/scheduled ?tomorrow) ?scheduled]
    [(get-else $ ?b :block/deadline ?tomorrow) ?deadline]
    (or 
      [(contains? #{"NOW", "DOING"} ?m)]
      [(<= ?scheduled ?today)]
      [(<= ?deadline ?today)])]
:inputs [:today :1d-after]
:result-transform (fn [result]
  (sort-by (fn [h]
    (get-in h [:block/scheduled])) result))
:breadcrumb-show? false}
#+END_QUERY
12

seems like actually the 1d-after is broken in 7.0.0.
See the comments here on github.

Consider simply replacing :1d-after with 99990101 (01 January 9999). But if you are still around at that date, don’t forget to update the queries :slight_smile:

Hence, the result is:

#+BEGIN_QUERY
{ :query [:find (pull ?b [*])
  :in $ ?today ?tomorrow
  :where
    [?b :block/marker ?m]
    (not [(contains? #{"DONE", "CANCELED"} ?m)])
    [(get-else $ ?b :block/scheduled ?tomorrow) ?scheduled]
    [(get-else $ ?b :block/deadline ?tomorrow) ?deadline]
    (or 
      [(contains? #{"NOW", "DOING"} ?m)]
      [(<= ?scheduled ?today)]
      [(<= ?deadline ?today)])]
:inputs [:today 99990101]
:result-transform (fn [result]
  (sort-by (fn [h]
    (get-in h [:block/scheduled])) result))
:breadcrumb-show? false}
#+END_QUERY
1 Like
13

:1d-after is deprecated, prefer :+1d (or :tomorrow).

1 Like