Advanced query to find all pages with property storing journal page reference in the past

Questions & Help Queries
1

Hello there,

I aim to review my project pages on regular basis. For that, each of my project has a property “:spiral_calendar:” storing a reference to only one journal page. The idea is to identify during my weekly review, the projects with a “date” in the past.

I tried a set up and advanced query but it does not work (as you can imagine) and my guess is that I don’t grasp the complexity of reference page when comparing dates.

#+BEGIN_QUERY
{:title ["🎯projects to review"]
 :inputs [:today]
 :query [:find (pull ?p [*])
   :in $ ?today
   :where
     [?p :block/tags ?t]
     [?t :block/name ".project"]
     [?b :block/page ?p]
     [?p :block/properties ?prop]
     [(get ?prop :🗓️) ?review]
     [(> ?review ?today)]
 ]
 :collapsed? false
}
#+END_QUERY

I can use a simple query to list all the project pages and sort by clicking on the “:spiral_calendar:” column .. but I would really know what I’m missing above.

Thanks in advance !

2
  • This doesn’t say much.
  • :today returns a value like 20251019
    • Unless the value of 🗓️ follows that format, it won’t play well.
      • However, you can get the properly formatted date from the journal page with :block/journal-day
3

Hello mentaloid,

you’re right about the lack of detail in “does not work” behaviour description :joy:

let me add some details: I created 2 project pages where the “:spiral_calendar:” property points to

  • project A: link to yesterday’s journal page
  • project B: link to tomorrow’s journal page

with the query I got the 2 projects listed as result

image
image1636×334 23.2 KB

if I revert the comparison

[(< ?review ?today)]

no page is listed.

I remember a post with

:block/journal-day

I will dig to see how to convert the value of my property to this format…

Thanks!

4

this query works - not sure it’s the best format

#+BEGIN_QUERY
{:title [“:bullseye: Related projects”] :inputs [:today]
:query [:find (pull ?p [*])
:in $ ?today
:where [?p :block/tags ?t][?t :block/name “.project”]
[?b :block/page ?p][?p :block/properties ?prop] [(get ?prop ::spiral_calendar:) ?review]
[?j :block/original-name ?journal][(contains? ?review ?journal)]
[?j :block/journal-day ?d][(> ?d ?today)]
] :collapsed? false}
#+END_QUERY

image
image1248×260 14.4 KB

if I reverse the comparison (just in case I was lucky):

#+BEGIN_QUERY
{:title [“:bullseye: Related projects”] :inputs [:today]
:query [:find (pull ?p [*])
:in $ ?today
:where [?p :block/tags ?t][?t :block/name “.project”]
[?b :block/page ?p][?p :block/properties ?prop] [(get ?prop ::spiral_calendar:) ?review]
[?j :block/original-name ?journal][(contains? ?review ?journal)]
[?j :block/journal-day ?d][(< ?d ?today)]
] :collapsed? false}
#+END_QUERY

got the expected result:

image
image1174×264 14.7 KB

thanks to this post: Query page property by date range - #4 by Siferiax :slight_smile: