Hello all! I’m a former software engineer turned homeschool parent. I have some questions. In a relational database I might have called them database schema questions, but in Logseq they might be query questions or maybe property questions instead.
I used to use Airtable to track the books that my kids read (this information is helpful for end of year reporting). Querying Airtable and exporting data from it are easy. Getting that data actually input from my phone has been more difficult so I am trying to use Logseq instead, since we’re already using it for a variety of other purposes.
As my kids finish reading books, I add Reading Log entries to the daily journal. They end up reading the same books, sometimes more than once, which means that I end up entering the same book-related information repeatedly. The relational database part of my brain doesn’t like that.
How can I reduce the data duplication? It seems like I could make a page for each book they read, and make page properties for author, title, etc. The difficulty comes when I want to combine that information with information from the Reading Log in a query result table. I haven’t been able to get a query to return information from more than one block type.
tl;dr: How do I reduce data duplication in block properties. Can I retrieve properties from child blocks and combine them with parent block properties?
If you want any details of how things work, I’m happy to explain.
You can also checkout the datomic page here: https://docs.datomic.com/on-prem/query/query.html
(Logseq uses datascript and not datomic, but it’s a pretty similar implementation of datalog)
Ps. There are other options to solve this as well, which uses more complicated :result-transform functions so you don’t need a :view but can use Logseq’s own query tables.
I don’t know what you would prefer.
I kept seeing example queries using the pull macro/method, but I wasn’t able to get that to return the fields I wanted. This query example more closely matches the datalog tutorials I was reading.
Yes, pull is usually fine for most purposes.
You can use pull here too, (pull ?b [*]) (pull ?p [*]), however this gives two rows in the table.
You need to then use the merge clojure function in the result-transform to bring them together.
Though clojure is generally a bit too advanced for me
I’ve been playing with the solution you provided and I think I understand how much of it works. I’m not certain that I want to use a view to produce a table, though. I’m interested in using the Logseq query tables so that dynamic sorting is an option.
When I comment out the view logic, I get back a list of maps.
Is there a way to load those maps into a Logseq query table? I’m guessing that the pull method is involved in that, but I’m still not sure how to get data from a parent and child block into the result table when using pull.
Here’s what I’m using to generate those maps:
#+BEGIN_QUERY
{
:title [:h4 "Reading list for all readers"]
:query [
:find ?n ?author ?started ?completed ?tags
:keys book author started completed tags ; define the map for each row returned
:where
[?b :block/properties ?prop] ; load the properties of block ?b into ?prop
[(get ?prop :started) ?started] ; load the value of the started property into ?started
[(get ?prop :completed) ?completed] ; load the value of the completed property into ?completed
[(get ?prop :book) ?bk] ; load the link to the book page into ?bk
[(get ?prop :tags) ?tags] ; load the value of the tags property into ?tags
[?p :block/original-name ?n] ; load the name of page ?p into ?n
[(contains? ?bk ?n)] ; does the page name match the name of the book?
[?p :block/properties ?pageprop] ; load the properties of the page into ?pageprop
[(get ?pageprop :author) ?author] ; get the author from the book page
]
}
#+END_QUERY
The three seperate blocks shown at the top are combined to one row in the query result below it.
This query uses :find (pull ?b[*]) and the :result-transform I included below.
I don’t know if it can be modified to do something with :find (pull ?b [*]) (pull ?p [*]).
But I think that’s the direction for your solution.
:result-transform
(fn [res]
(sort-by
(fn [s]
(get-in s [:block/properties :journal-day])
)
>
(vals
(reduce
(fn [a c]
(let [id (get-in c [:block/page :db/id])]
(if
(get a id)
(assoc a id
(update
(get a id)
:block/properties
(fn [u]
(merge
u (:block/properties c)
{:journal (get-in c [:block/page :block/name])
:journal-day (get-in c [:block/page :block/journal-day])
}
)
)
)
)
(assoc a id c)
)
)
)
{} res
)
)
)
)
