How to structure blocks to prevent repetition of property data

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.

I have a template defined for a [[Reading Log]].

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?


Yeah should be possible.
It’s a bit more complicated than a straight forward query.
I made you an example.


{:title [:h4 "Reading list for example reader"]
 :query [:find ?n ?author ?started
  :keys book author started
   [?b :block/properties ?prop]
   [(get ?prop :started) ?started]
   [(get ?prop :book) ?bk]
   [?p :block/original-name ?n]
   [(contains? ?bk ?n)]
   [?p :block/properties ?pageprop]
   [(get ?pageprop :author) ?author]
   [?r :block/name "example reader"]
   [?l :block/name "reading log"]
   [?b :block/refs ?r]
   [?b :block/refs ?l]
 :result-transform (fn [result] (sort-by (fn [r] (get-in r [:started])) > result) )
 :view (fn [rows] [:table [:thead [:tr  
    [:th "Book"] [:th "Author"] [:th "Started"] ] ] 
 [:tbody (for [r rows] [:tr 
    [:td [:a {:href (str "#/page/" (get-in r [:book]) )} (get-in r [:book])] ]
    [:td (get-in r [:author]) ]
    [:td (get-in r [:started]) ]
 ] ) ]
 ] )

If you want any details of how things work, I’m happy to explain.
You can also checkout the datomic page here:
(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.


Thank you - that is so helpful!

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.

1 Like

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 :sweat_smile:

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:

    :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
        [?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

So there is a very complicated :result-transform that honestly I don’t understand.
It merges different blocks together.

Here’s an example of what it does:

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.

(fn [res] 
   (fn [s] 
     (get-in s [:block/properties :journal-day])
     (fn [a c] 
       (let [id (get-in c [:block/page :db/id])] 
           (get a id) 
           (assoc a id 
                   (get a id) 
                   (fn [u] 
                      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 
1 Like