I have been trying to learn sql to do queries –
My question is – when I join query result from one table to another - the result is a cartesian product of the elements of the first table with the elements of the second table if the second table happens to have more than 1 element in the column in question
That is
(org-roam-db-query [:select [t0:c0 t0:c1 t1:c2]
:from t0
:inner :join t1 :on (= t0:common-column t1:common-column)
])
where 'ti' represents the ith table
and 'ci' represents arbitary columns
the result shall be
((c01 c11 c21) (c01 c11 c22) (c01 c11 c23) ... )
=> column c2 on table t1 has multiple entries in the same row
What I want to achieve is the following
((c01 c11 '(c21 c22 c23)) ... )
As a practical example consider querying all the links associated with and id
(let* ((sql `[:select [nodes:title
nodes:file
links:dest
]
:from nodes
:inner :join links :on (= nodes:id links:source)
]))
(org-roam-db-query sql))
The only way I can make the data more structured is by using a hash-table to group out the common elements
(let* ((sql `[:select [nodes:title nodes:file
links:dest links:type] ; SQL query to select title, file, and destination from nodes and links tables
:from nodes
:inner :join links :on (= nodes:id links:source)]) ; Join nodes and links tables on their IDs
(results (org-roam-db-query sql)) ; Execute the SQL query and store the results
(grouped-results (make-hash-table :test #'equal))) ; Initialize a hash table for grouping
;; Group the results by common elements (title and file)
(dolist (result results) ; Iterate over each result from the SQL query
(let* ((title (nth 0 result)) ; Extract the title from the result
(file (nth 1 result)) ; Extract the file from the result
(dest (nth 2 result)) ; Extract the destination from the result
(type (nth 3 result))
(path (concat type ":" dest))
(common-elements (list title file))) ; Create a list of common elements (title and file)
;; Add the destination to the list associated with the common elements
(if (gethash common-elements grouped-results) ; Check if common elements already exist in the hash table
(push path (gethash common-elements grouped-results)) ; If yes, append the destination to the list
(puthash common-elements (list path) grouped-results)))) ; If not, create a new entry in the hash table
;; Print the grouped results
(maphash
(lambda (common-elements destinations) ; Iterate over each entry in the hash table
(message " ---------- -----------") ; Print separator
(message "Common Elements: %S" common-elements) ; Print the common elements
(message " ---------- -----------") ; Print separator
(dolist (destination destinations) ; Iterate over each destination associated with the common elements
(message "Destination: %S" destination)) ; Print each destination
(message " ")) ; Print a newline to separate groups
grouped-results))
Please let me know if there are more efficient ways of doing this. Thanks.