Hello - struggling a bit with datalog queries.
I have pages with “type:: Person”
Inside I have multiple blocks with “date:: 2024-05-01” and “type:: Meeting”
I want a query that is the Page and the latest date present on a block of type Meeting.
I can basically get the query right, but I’m struggling on ordering and filtering - any help on how I ought to be doing this ?
Welcome. Mind sharing your current query?
I’m hacking around, but
#+BEGIN_QUERY
{:title [:h3 "Last meets"]
:query [:find (pull ?b [*])
:where
[?b :block/properties ?prop]
[(get ?prop :date) ?at]
[?b :block/page ?p]
(page-property ?p :type "Person")
]
;; Trying to fiddle around with this:
:result-transform (fn [result] (sort-by (fn [r] (get-in r [:block/page ])) result ))
}
#+END_QUERY
I don’t know if I ought to be trying to attack result-transform to do an ordering on “?at” and then somehow filtering out just the 1st time a page is mentioned in the results, or if there’s a better way.
Do you expect a single result for the latest meeting among all the pages? Or a list of pages with the date of the latest meeting of each one of them?
This:
Or a list of pages with the date of the latest meeting of each one of them?
I think I’m a bit confused as to what the shape of the data that comes out of the query is going into result-transform (or even :view)
Then let’s begin with something that can be useful:
#+BEGIN_QUERY
{:title [:h3 "Last meets"]
:query [:find ?name (max ?at)
:keys name at
:where
[?b :block/properties ?prop]
[(get ?prop :date) ?at]
[?b :block/page ?p]
(page-property ?p :type "Person")
[?p :block/name ?name]
]
:result-transform (fn [result] (map
(fn [r] (str (get-in r [:name]) " at " (get-in r [:at]) ) )
result ))
}
#+END_QUERY
Oh, that’s very cool 
If I wanted to produce output that could be rendered in the default table style (such as a hyperlink to the page, or the lists that might show the block content itself), what would be the best approach?
My SQL brain is thinking of subqueries, but that feels like it’s guiding me in the wrong direction/
SQL background doesn’t help too much. You need to do some reading here in the forums and later some testing. There are many non-obvious options available. Chances are that, as you read what other people have done, you will reconsider what you want to do yourself. Then you can come back with more focused questions.
Well, the challenge seems to be that if the options are non-obvious, they’re very difficult to search for, particularly if the actual options available seem hard to discover.
Is passing the results into a secondary query an option ?
If not, the only other option I can think of is fetching all the blocks, and doing sort, then filter out duplicates.
Logseq’s advanced queries work in stages:
:where
:result-transform
:view
As you may notice, there is plenty of overlapping. There is no specific way of doing things. This results in:
So, here’s where I landed, in case anyone else walks this way:
#+BEGIN_QUERY
{:title [:h3 "Last meeting by person"]
:query [:find ?pageId ?at ?blockId (pull ?b [*])
:keys pageId at blockId zzt
:where
[?b :block/properties ?prop]
[(get ?prop :date) ?at]
[?b :block/page ?p]
(page-property ?p :type "Person")
[?p :block/name ?name]
[?p :block/uuid ?pageId]
[?b :block/uuid ?blockId]
]
:result-transform (fn [rr]
(defn filter-sequential-duplicates
[v key]
(let [reduce-fn (fn [[result last-val] current-map]
(let [current-val (get current-map key)]
(if (= current-val last-val)
[result last-val]
[(conj result current-map) current-val])))]
(first (reduce reduce-fn [[] nil] v))))
(defn transform-maps [vec-of-maps]
(mapv (fn [m]
(-> (get m :zzt)
))
vec-of-maps))
(transform-maps (filter-sequential-duplicates ( sort-by
(juxt (fn [p] (get p :pageId)) (fn [a] (get a :at) ) )
(fn [a b] (compare b a))
rr ) :pageId )
) )
}
#+END_QUERY
(Be gentle, this is day-1 for clojurescript for me).
It’s basically “fetch all the blocks” then transform by "sort it by page and then block date descending, filter out things in the results that are replicated, then pull out just the block so it renders nicely in the table viewer.
I don’t know if the query necessarily has to be that complicated. But tbh I very much don’t understand clojurescript.
One thing to note though is that datalog implicitly does a distinct.
And a helpful tip might be to add this:
:view (fn [r] [:pre.code (pprint r)])
Which will make Logseq spit out the “actual” code results of a query.
Also useful when doing :find (pull ?b [*]) as it gives all attributes and their values for entity ?b.
It has helped me a lot anyway.
I also came to datalog with an SQL background and you really need to unlearn default SQL ideas. Like subqueries.
The easiest way for me to think on it was we are querying 1 big table with just 3 columns, entity-id, attribute, value. Whereby in SQL we would have table, column and column value.
In datalog we don’t need joins to query across tables, however that may lead to some Cartesian products along the way.
So for me I first try to get the query to spit out whatever data I will need for my result. Unsorted, but distinct and complete. (Like @mentaloid using a (max) function)
Then I try to get the data to present in a way I wish for.
This may be going back and instead of that (max) function, return all data and then use a (map) function in the result-transform.
It has helped me a lot in learning to just take it one step at a time and check the results as I go. It’s what I still do really.