Get block text from advanced query and sum total

Questions & Help
1

Ok, diving into advanced query I found something I don’t know what I’m doing wrong.

I have a block that look like this:

[[Youtube Premium]] 
clase:: gasto
importe:: -17.99
categoría:: #subscripciones

I have this on a template and it’s to track expenses from my journal.

Now I have an expenses page, and I can use

{{query (property :clase "gasto")}}

And as a child a block to show the total sum

**Total Importe:** {{function (sum :importe)}}

And it works like a charm, but I want to learn clojure/datalog, and want to do it with advanced queries and customize the result.

I ended with this advanced query

{:title [:h3 "Gastos del mes"]
 :query [:find (pull ?b [*])
         :where
         (property ?b :clase "gasto")]
:view (fn [rows] [:table 
 [:thead 
  [:tr 
   [:th "Concepto"] 
   [:th "Importe"]
  ]
 ] 
 [:tbody 
   (for [r rows] 
      [:tr 
      [:td (get r [:block/content])] ; this is my issue, just tried this after several tests to see if this works
      [:td (get-in r [:block/properties :importe])]
      ]
   )
]])
}

And it works, but I can’t get in the first column (Concepto) to show the “content” of the block (on my previous example, the text [[Youtube Premium]]

Doubts:

  • How can I get the content/text of the block?
  • Is this advanced query code old? I got it from a published graph on internet, and I didn’t saw the use of tables and get-in here in the forum.
  • It’s possible to get at the end of the table, an empty record showing at “Importe” column the sum of all values?
2

Can drop the entire view in favor of the use of the table option.
However the way to show total remains the same if you also want the details.
It is possible to give a total only though.

Here’s a modified “true” advanced query for what you’re trying to do.

{:title [:h3 "Gastos del mes"]
 :query [:find (pull ?b [*])
  :where
   [?b :block/properties ?prop]
   [(get ?prop :clase) ?clase]
   [(= ?clase "gasto")]
   [?b :block/page ?p]
 ]
}

Can use this little icon to change the result to a table:
image

Query to get the sum of all of [[Youtube Premium]]:

{:title [:h3 "Gastos Youtube Premium"]
 :query [:find (sum ?importe)
  :where
   [?y :block/name "youtube premium"]
   [?b :block/refs ?y]
   [?b :block/properties ?prop]
   [(get ?prop :clase) ?clase]
   [(= ?clase "gasto")]
   [(get ?prop :importe) ?stringimporte]
   [(* 1 ?stringimporte) ?importe] ;convert the value to a number
   [?b :block/page ?p]
 ]
}
1 Like
3

Issue with that you can’t change easily order of fields and captions of the column, then, shows importe (lowercase) and “block” (instead Concepto)

Oh I thought you can embed “formulas” in the query for proper formating

4

Ah! Sure :slight_smile:
So to explain, this is what all backend information about your block looks like:

{:block/properties-text-values
 {:clase "gasto", :importe "-17.99", :categoría "#subscripciones"},
 :block/uuid #uuid "6679b4e9-97e2-40fd-9ade-094c451a9b8e",
 :block/properties
 {:clase "gasto", :importe "-17.99", :categoría #{"subscripciones"}},
 :block/journal? true,
 :block/left {:db/id 2336},
 :block/refs
 [{:db/id 2424}
  {:db/id 2425}
  {:db/id 2426}
  {:db/id 2427}
  {:db/id 2428}],
 :block/properties-order (:clase :importe :categoría),
 :block/journal-day 20240624,
 :block/format :markdown,
 :block/content
 "[[Youtube Premium]] \nclase:: gasto\nimporte:: -17.99\ncategoría:: #subscripciones",
 :db/id 2423,
 :block/path-refs
 [{:db/id 2335}
  {:db/id 2424}
  {:db/id 2425}
  {:db/id 2426}
  {:db/id 2427}
  {:db/id 2428}],
 :block/parent {:db/id 2335}, 
 :block/page {:db/id 2335}}

Never mind the exact id’s they are specific to my graph.
You can see that :block/content contains everything. If we’re only interested in the first line, we’ll need to do some manipulation.

{:title [:h3 "Gastos del mes"]
 :query [:find (pull ?b [*])
  :where
   [?b :block/properties ?prop]
   [(get ?prop :clase) ?clase]
   [(= ?clase "gasto")]
   [?b :block/page ?p]
 ]
 :view (fn [rows] [:table
   [:thead [:tr   [:th "Concepto"] [:th "Importe"]]]
   [:tbody (for [r rows
            :let [content (str (get r :block/content))]
            :let [displayline (first (str/split-lines content))]
    ]
           [:tr
             [:td [:a {:href (str "#/page/" (get r :block/uuid))} displayline]]
             [:td (get-in r [:block/properties :importe])]
           ])
   ]])
}

Can click on the content in column Concepto to go to the actual block.

Only as far as my last query I posted goes. There are some other ways. It depends on what you wish for. I just don’t know of a way to do so in a details table.