Add correlated page property to query table blocks output (like join operation in sql, adding lookup table columns)

Questions & Help
1

Hi,
I have the following server pages
TexasServer.md
(page properties)
type:: [[server]]
env:: [[Prod]]
ArizonaServer.md
(page properties)
type:: [[server]]
env:: [[QA]]

and database page
AdventureDB.md
(page properties)
type:: [[DB]]
(block 1 - represent server related to this db)
server:: [[ArizonaServer]]
(block 2 - represent another server related to this db)
server:: [[TexasServer]]

This query shows all DB servers blocks and I wish to add also other server page properties, e.g. env
What is the easiest way to achieve that?
Appreciate any help
Thanks

#+BEGIN_QUERY
{:title "databases"
  :query [:find (pull ?b
         [*])
      :where
 (has-property ?b :server)
]
}
#+END_QUERY

image
image1533Ă—729 51.7 KB

2

Yes… gets a bit complicated, but this worked when I tested it:

#+BEGIN_QUERY
{:title "databases"
 :query [:find ?env (pull ?b [*])
   :keys env block
   :where
     (has-property ?b :server)
     [?b :block/refs ?server]
     [?server :block/properties ?prop]
     [(get ?prop :env) ?env]
 ]
 :result-transform (fn [result] (map (fn [m] (update (:block m) :block/properties (fn [u] (assoc u :env (get-in m [:env]) ) ) ) ) result ) )
}
#+END_QUERY

image
image829Ă—177 12.9 KB

2 Likes
3

Many thanks, really appreciated!

I started to read and practice a bit clojure functions manipulation, it is a bit challenging :slight_smile:
I have deep knowledge with sql but datalog and clojure don’t come easy…

In addition, writing advanced query in logseq is not easy (cursor issues when query need to be scroll, indentation, hard to detect closing ellipsis, no auto complete… )
I know about code block clojure formar trick with Ctrl shift, that is handy,
But I wonder if there’s a better way for writing advanced query (e.g. some ide? Using nbb cli?)

4

You’re welcome.
I come from SQL myself as well. It’s a bit of a mind twist to be sure! It requires thinking differently about the questions to ask of the database.
It gets even more complex when you get into rules and clojure functions.
I found Datomic to be a good resource for myself. Even though Logseq uses Datascript datalog and not Datomic, most of it is the same.
https://docs.datomic.com/on-prem/query/query.html

I don’t know about writing queries a different way. I have so far always used Logseq text editor and dealt with the shortcomings lol.

Oh bonus tip! Database schema:

1 Like