Hi
I have a block which contains the result of a query - a date. But this block displays the date as a number.
Now I want to create a query that uses that block (it’s the parent block) as an input, and compares the block’s date against another date. But the date is a number…
How can I convert that number to a date for the query?
Thanks!
Could you provide specific examples of those numbers, dates, and maybe the query under construction itself?
Sure
So I started setting a property ‘futuremeeting’ with value as a name of a person
I have a template for a 1v1 meeting with that person
Inside the template I run a query getting all the blocks which has that property and that person’s name.
The problem is, I’m getting all the blocks, even the old ones, i.e. even the ones older than the last meeting with him.
So I’m trying to run a query that will get all the blocks with the property, where the block’s date is newer than the date in which the last 1v1 meeting took place.
I couldn’t find a way to do that in 1 query, so I divided it:
Here’s the first query:
#+BEGIN_QUERY
{
:title [:b "🧨 max meeting day"]
:query [
:find (max ?meetingday)
:where
[?b :block/page ?p]
[?p :block/journal? true]
[?b :block/content ?content]
[(clojure.string/includes? ?content "[[Stas]] #1v1")]
[?p :block/journal-day ?meetingday]
]
:breadcrumb-show? true
}
#+END_QUERY
The result is: 20240528
Now I want to use it as an input and convert that number to a date, so that I would be able to run this query:
#+BEGIN_QUERY
{
:title [:b "🧨 sub query"]
:query [
:find (pull ?c [*])
:in $ ?lastdateblock
:where
[?c :block/page ?pc]
[?pc :block/journal? true]
[?pc :block/journal-day ?cday]
(property ?c :futuremeeting "Stas")
[(> ?cday ?lastdateblock)]
]
:breadcrumb-show? true
:inputs [:parent-block]
}
#+END_QUERY
But the results returned contains blocks with that property and value, which are older than that date (May 28th 2024)
:parent-block doesn’t return a date. This can be confirmed with a query like this one:
#+BEGIN_QUERY
{:query [:find ?lastdateblock
:in $ ?lastdateblock
]
:inputs [:parent-block]
}
#+END_QUERY
It is not possible to divide queries like that. To combine the queries, try something like this:
#+BEGIN_QUERY
{
:query [:find (pull ?c [*])
:where
[?p :block/journal-day ?maxmeetingday]
[?b :block/page ?p]
[?b :block/content ?content]
[(clojure.string/includes? ?content "[[Stas]] #1v1")]
(not-join [?maxmeetingday]
[?p :block/journal-day ?meetingday]
[(> ?meetingday ?maxmeetingday)]
[?b :block/page ?p]
[?b :block/content ?content]
[(clojure.string/includes? ?content "[[Stas]] #1v1")]
)
[?pc :block/journal-day ?cday]
[?c :block/page ?pc]
(property ?c :futuremeeting "Stas")
[(> ?cday ?maxmeetingday)]
]
:breadcrumb-show? true
}
#+END_QUERY
Wow. Amazing, thank you!!
I spent so much time trying to get this to work.
Do you have a guide / doc reference for advanced queries? All the searches I made and I’ve never seen the ‘join’ parts
Thanks again!
Is it possible to use this to calculate a streak?
In the journal I would have blocks with a page link to wandelen.
Would be nice to see how many days my current streak of walking every day is.
My initial idea was to get where the streak starts and then count the number of days until yesterday.
However a double (not ) doesn’t work.
[?p :block/name "wandelen"]
[?jmax :block/journal-day ?maxday]
[(< ?maxday ?today)]
(not
[?bmax :block/page ?jmax]
[?bmax :block/refs ?p]
)
(not-join [?maxday]
[?j :block/journal-day ?day]
[(> ?day ?maxday)]
(not-join [?j]
[?b :block/page ?j]
[?b :block/refs ?p]
)
)
This yields no results.
I have a query to see when last my streak broke. Or in other words after which day my streak would be starting again.
#+BEGIN_QUERY
{:title "Streak starts after"
:inputs [:today]
:query [:find (pull ?j [*])
:in $ ?today
:where
[?p :block/name "wandelen"]
[?j :block/journal-day ?d]
[(< ?d ?today)]
(not
[?b :block/page ?j]
[?b :block/refs ?p]
)
]
:result-transform (fn [result] [(first (sort-by (fn [s] (get s :block/journal-day)) > result) )] )
:view (fn [rows] (for [r rows] [:a {:href (str "#/page/" (get r :block/original-name))} (get r :block/original-name)] ))
}
#+END_QUERY
Which I then manually input into another query
#+BEGIN_QUERY
{:title "Streak"
:inputs [20240701 :today]
:query [:find (count ?day)
:in $ ?start ?today
:where
[?p :block/name "wandelen"]
[?j :block/journal-day ?day]
[(< ?start ?day ?today)]
[?b :block/page ?j]
[?b :block/refs ?p]
]
:view (fn [rows] (for [r rows] (str r " dagen") ))
}
#+END_QUERY
Because of your example, I was working on combining the two. But as said, double not is not working.
Any ideas?
not-joins would also pass ?p[(> ?day ?maxday)] , because it returns today.
[(> ?today ?day ?maxday)]Thanks! That was exactly the problem. It works now 
Here’s the final query
#+BEGIN_QUERY
{:title [:b.h.light [:span.mr "➿"] "Streak"]
:inputs [:query-page :today]
:query [:find (count ?day)
:in $ ?qpage ?today
:where
[?p :block/name ?qpage]
[?jmax :block/journal-day ?maxday]
[(< ?maxday ?today)]
(not
[?bmax :block/page ?jmax]
[?bmax :block/refs ?p]
)
(not ;-join [?maxday ?p ?today]
[?jmin :block/journal-day ?minday]
[(> ?today ?minday ?maxday)]
(not
[?bmin :block/page ?jmin]
[?bmin :block/refs ?p]
)
)
[?j :block/journal-day ?day]
[(< ?maxday ?day ?today)]
[?b :block/page ?j]
[?b :block/refs ?p]
]
:view (fn [rows] (for [r rows] (str r " dagen") ))
:result-transform (fn [result] (sort result))
}
#+END_QUERY
It doesn’t perform, so might need some more restrictions on the data.
I don’t have enough data to measure performance, but you may:
j/jmin/jmax down to files with :block/file , before checking their date[(< ?maxday ?today)] , e.g. [(< ?before30days ?maxday ?today)]I checked, I have 864 journal pages, of which 860 have a file 
But! Setting that lower limit did the trick.
It now returns fast enough. I didn’t measure, but it is near instant instead of loading for a number of seconds.
Thanks a bunch! (sorry for being terribly slow in responding)
Some queries could borrow that quote for themselves…