Reputation: 11
I am trying to create a list of maps with :node_name
, :children
and :is_leaf
keys. When children is not nil, it would also create a nested map with the same keys. Things work if the :children
key is just a simple list of names of the nodes where the nesting level is nlevel(current_node) + 1
, but things start to break when I try to recursively create a nested map of children for each node that is not a leaf node instead. On a side note I'm also looking for ways to reduce the number of times the query is evaluated within the recursive calls. My data flow that works is the following:
; read category relationships from a materialized view holding all categories
(def category-tree
(let [query "SELECT id::integer, \"name\"::varchar, path_string::text
FROM public.category_tree_full;"
result (future (jdbc/execute! @connect-database [query]))]
@result))
; get the human-readable ltree paths, which can be something like Europe -> Germany -> Bavaria -> Munich
(def path-strs (mapv #(s/split % #" -> ") (mapv #(:path_string %) category-tree)))
(def leaf-nodes
"returns categories with no child categories"
(let [intermediaries (set (mapcat butlast path-strs))
last-elems (set (mapv last path-strs))]
(difference last-elems intermediaries)))
(defn get-immediate-children [node-name]
"will return a vector of all nodes where nesting level is the
nlevel of the current node and where the path starts with it's ID"
(let [node-id (get-category-id node-name)
node-path (:pv (jdbc/execute-one! @connect-database
["SELECT path::text AS pv FROM category c
WHERE c.id = ?" node-id]))
node-level (:nlevel
(jdbc/execute-one!
@connect-database
["SELECT nlevel(path) FROM category c WHERE c.id = ?" node-id]))
children (or (map #(:subcategory_name %) (jdbc/execute! @connect-database
[(s/replace (format "WITH subcategories AS (SELECT path, \"name\"::varchar as subcategory_name
FROM category
WHERE nlevel(path) = %d AND path ~ '%s.*')
SELECT s.subcategory_name
FROM subcategories s
JOIN category c2 ON subpath(s.path, 0, nlevel(s.path) - 1) = c2.path;" (inc node-level) node-path) #"\n" " ")] {:builder-fn as-unqualified-maps})) nil)]
children))
; pull the data from one of the materialized views for primary roots
(defn from-view [view-name] (jdbc/execute! @connect-database
[(format "SELECT name::varchar, path_string::varchar
FROM %s" view-name)] {:builder-fn as-unqualified-maps}))
(defn process-data [from-view]
(let [split-path (fn [s] (clojure.string/split s #" -> "))
path-map (zipmap (map :name from-view) (map #(split-path (:path_string %)) from-view))
is-leaf (fn [node] (some #(= node %) leaf-nodes))
children (fn [node] (when-not (is-leaf node) (get-immediate-children node)))]
(map (fn [[node]]
{:node_name node
:children (when-not (is-leaf node) (children node))
:is_leaf (or (is-leaf node) false)})
path-map)))
But changing the children
let binding in the process-data
function to
children (fn [node] (let [cvalues (get-immediate-children node)]
(if (seq cvalues)
(map (fn [[child]]
{:leaf (is-leaf child)
:name child
:children (when-not (is-leaf child) (get-immediate-children child))}) cvalues)
nil)))
causes postgresql to yell at me about the hstore extension not being installed. I cannot clearly understand how would hstore be related to this. Is my recursive map
anonymous function wrong? As stated, I'm looking for ways to further minimize the number of times the query from get-immediate-children
would be run just to create a nested structure. Maybe some ltree and json functions can be useful here, but then there'd be the overhead of the (de)serialization. Sorry if this feels noobish but I have only been programming in Clojure for a couple months or so after working mostly with imperative solutions.
Upvotes: 1
Views: 83