Ok so my query is basically a performance nightmare.
What I’m trying to do is get a rid of journal entries like:
[[gaming]] : [[World of Warcraft]] and just use [[World of Warcraft]].
On my hobby page I have a “last time I did…” query.
But that stops working as it will not know that [[World of Warcraft]] is a hobby.
I tried telling the query that… but it crashes Logseq.
The moment I uncomment the not, it crashes.
#+BEGIN_QUERY
{:title [:b.h.dark "Laatste keer dit jaar"]
:inputs ["hobby" :yesterday]
:query [:find (pull ?p [*])
:in $ ?qpage ?dag
:where
[?a :block/name "activiteit"]
[?p :block/properties ?prop]
[(get ?prop :cluster) ?cluster]
[(= ?cluster ?qpage)]
[?p :block/name]
(or-join [?b ?p]
[?b :block/refs ?p]
(and
[?p :block/original-name ?hobby]
[?subp :block/properties ?prop]
[(get ?prop :hobby) ?hobbyprop]
(or
[(= ?hobbyprop ?hobby)]
[(contains? ?hobbyprop ?hobby)]
)
[?b :block/refs ?subp]
)
)
(not
[(get ?prop :status) ?status]
[(= ?status "archief")]
)
[?b :block/path-refs ?a]
[?b :block/page ?j]
[?j :block/journal-day ?d]
[?j :block/original-name ?journal]
[(str ?d) ?jd]
[(subs ?jd 0 4) ?journaljaar]
[(str ?dag) ?strdag]
[(subs ?strdag 0 4) ?jaar]
[(= ?journaljaar ?jaar)]
; (not
; (or-join [?x ?p]
; [?x :block/refs ?p]
; (and
; [?p :block/original-name ?hobby]
; [?subp :block/properties ?prop]
; [(get ?prop :hobby) ?hobbyprop]
; (or
; [(= ?hobbyprop ?hobby)]
; [(contains? ?hobbyprop ?hobby)]
; )
; [?x :block/refs ?subp]
; )
; )
; [?x :block/path-refs ?a]
; [?x :block/page ?jx]
; [?jx :block/journal-day ?dx]
; [(> ?dx ?d)]
; )
]
}
#+END_QUERY
The original (working) query was like so:
#+BEGIN_QUERY
{:title [:b.h.dark "Laatste keer dit jaar"]
:inputs [:query-page :today]
:query [:find (pull ?p [*]) ?d ?journal
:keys page dag journal
:in $ ?qpage ?dag
:where
[?a :block/name "activiteit"]
[?p :block/properties ?prop]
[(get ?prop :cluster) ?cluster]
[(= ?cluster ?qpage)]
(not
[(get ?prop :status) ?status]
[(= ?status "archief")]
)
[?b :block/refs ?p]
[?b :block/path-refs ?a]
[?b :block/page ?j]
[?j :block/journal-day ?d]
[?j :block/original-name ?journal]
[(str ?d) ?jd]
[(subs ?jd 0 4) ?journaljaar]
[(str ?dag) ?strdag]
[(subs ?strdag 0 4) ?jaar]
[(= ?journaljaar ?jaar)]
(not
[?x :block/refs ?p]
[?x :block/path-refs ?a]
[?x :block/page ?jx]
[?jx :block/journal-day ?dx]
[(> ?dx ?d)]
)
]
:result-transform (fn [result]
(sort-by
(juxt
(fn [r] (get-in r [:block/properties :dag]))
(fn [r] (get-in r [:block/name]))
)
(map (fn [x]
(update (:page x) :block/properties
(fn [u] (assoc u :dag (get x :dag) :journal (get x :journal)
:hobby (str
(get-in x [:page :block/properties :icon])
" "
(get-in x [:page :block/original-name]))
)
)
)
)
result
)
)
)
}
#+END_QUERY
I’m thinking of doing something in the result-transform instead… but my mind is just sludge lately and I can’t figure it out.
May I ask to take a step back and try looking at your post as if another user had posted it, asking for your help? Would you be able to provide any type of help, without firstly flooding them with requests for explanations, example data etc.? After mastering queries, the time has probably come to also master questions and generally looking at the world from that other side of life. The general effort is to minimize the cognitive load and overall guesswork of whoever may attempt to help. Not to mention that in doing so you may even find by yourself at least the cause of the problem.
Yeah, I just… nevermind.
I’ll have to reconsider my requirements when I’m in a better head space.
I guess I was too tired/annoyed when I posted this.
Requirements / Information
- I have a page called
hobby which has an overview in regards to all my hobbies
- On that page I would like to have a query to show me for each hobby when the last time was I did that hobby
- A hobby is defined as either
- has property
cluster:: hobby
- has property
hobby:: <name of hobby> or hobby:: [[<name of hobby>]]
- The property value is always the original name of the page, in some cases it is also actually linked with
[[ ]]
- The results need to be aggregated on the level of the hobby pages that have the
cluster:: property.
- The results should not use a
:view, but instead use the Logseq query table rendering so I can change out what columns I see on the fly.
- The query only needs to use data from the current year, to limit the amount of information it requests.
- The hobby is considered “done” when it has an entry on the journal page somewhere indented (any level) under a block that links to page
activiteit.
- I may add notes in regards to a hobby somewhere else on the journal page and those entries shouldn’t be considered as doing the hobby.
Example page structure
- page original name:
gaming
- has property
cluster:: hobby
- page original name:
World of Warcraft
- has property
hobby:: gaming
All pages follow this same structure. Let’s call them hobbies and subhobbies.
In the journal I would link either to a hobby directly or to one of its subhobbies.
For example:
- Page: July 7th 2024
- [[activiteit]]
- [[World of Warcraft]] spelen
[[World of Warcraft]] here being a subhobby with hobby:: gaming
- [[character name]] : activity done
Or
- Page: July 9th 2024
- [[activiteit]]
- Verder [[haken]] aan [[project name]]. Rij 5 afgerond.
[[haken]] here being a hobby with cluster:: hobby
Expected result
Solution
I found the solution by removing the (not ) clause from my query and then combining two map functions in the :result-transform.
#+BEGIN_QUERY
{:title [:b.h.dark "Laatste keer dit jaar"]
:inputs [:query-page :today]
:query [:find (pull ?h [*]) ?d ?journal
:keys hobby dag journal
:in $ ?qpage ?dag
:where
; get the hobby pages in ?p
; save the group in ?h
[?p :block/name]
[?p :block/properties ?prop]
(or-join [?qpage ?p ?prop ?h]
; the page is a hobby
(and
[(get ?prop :cluster) ?cluster]
[(= ?cluster ?qpage)]
[(ground ?p) ?h]
)
; the page is a subhobby
(and
[(get ?prop :hobby) ?hobby]
[?h :block/original-name ?name]
(or
[(= ?hobby ?name)]
[(contains? ?hobby ?name)]
)
)
)
[?h :block/original-name ?hobby]
; get the proper journal blocks
[?a :block/name "activiteit"]
[?b :block/refs ?p]
[?b :block/path-refs ?a]
[?b :block/page ?j]
[?j :block/journal-day ?d]
[?j :block/original-name ?journal]
[(str ?d) ?jd]
[(subs ?jd 0 4) ?journaljaar]
[(str ?dag) ?strdag]
[(subs ?strdag 0 4) ?jaar]
[(= ?journaljaar ?jaar)]
]
:result-transform (fn [result]
; sort the result first by journal day and then by hobby name
(sort-by
(juxt
(fn [r] (get-in r [:block/properties :dag]))
(fn [r] (get-in r [:block/name]))
)
; add the journal information as :block/properties so that
; 1. Logseq will render a table
; 2. the journal information is available within the table
(map
(fn [x] (update (:hobby x) :block/properties
(fn [u] (assoc u
:dag (get x :dag)
:journal (get x :journal)
:hobby (str (get-in x [:hobby :block/properties :icon]) " " (get-in x [:hobby :block/original-name]))
))
))
; sort the results by journal day descending and
; group them by hobby then
; only return the first value
(map (fn [[key value]] (first value))
(group-by (fn [r] (get-in r [:hobby :block/name]))
(sort-by (fn [r] (get r :dag)) > result)
)
)
)
)
)
}
#+END_QUERY
Yes it is simply the pile of data that is too large.
When I limit the initial query to less data it works fine.
I do wonder if use of the not is better in this case.
- it limits the data in the query result
- at what (performance) price?
- Is that price due to the way the query is written
- or just because of the huge amount of data we try to subtract from each other
- it saves on (double) sorting and grouping after the fact.
The only question is, can I get it to work on my large data set. I will have to (carefully!) test your suggestions.
This works, and performs about equal to my working solution.
The moment I change it to work with the full year of 2024 it crashes Logseq.
#+BEGIN_QUERY
{:title [:b.h.dark "Laatste keer dit jaar"]
:inputs ["hobby" "202407"]
:query [:find (pull ?p [*])
:in $ ?qpage ?dag %
:where
[?a :block/name "activiteit"]
[?p :block/properties ?prop]
[(get ?prop :cluster) ?cluster]
[(= ?cluster ?qpage)]
[?p :block/name]
(not
[(get ?prop :status) ?status]
[(= ?status "archief")]
)
(check-ref ?b ?p)
[?b :block/path-refs ?a]
[?b :block/page ?j]
[?j :block/journal-day ?d]
[?j :block/original-name ?journal]
[(str ?d) ?jd]
[(subs ?jd 0 6) ?journaljaar]
; [(str ?dag) ?strdag]
; [(subs ?strdag 0 4) ?jaar]
[(= ?journaljaar ?dag)]
(not-join [?a ?d ?p]
[?x :block/path-refs ?a]
[?x :block/page ?jx]
[?jx :block/journal-day ?dx]
[(> ?dx ?d)]
(check-ref ?x ?p)
)
]
:rules [
[(check-ref ?b ?p)
(or-join [?b ?p]
[?b :block/refs ?p]
(and
[?p :block/original-name ?hobby]
[?subp :block/properties ?prop]
[(get ?prop :hobby) ?hobbyprop]
(or
[(= ?hobbyprop ?hobby)]
[(contains? ?hobbyprop ?hobby)]
)
[?b :block/refs ?subp]
)
)
]
]
}
#+END_QUERY