Skip to content

Instantly share code, notes, and snippets.

@dmgerman
Last active May 20, 2024 21:11
Show Gist options
  • Save dmgerman/53e266502e4d53294d1e8be46af93b4f to your computer and use it in GitHub Desktop.
Save dmgerman/53e266502e4d53294d1e8be46af93b4f to your computer and use it in GitHub Desktop.
Improved Org Roam Node List
(defun org-roam-node-list ()
(let ((rows (org-roam-db-query
"SELECT
id,
file,
filetitle,
\"level\",
todo,
pos,
priority ,
scheduled ,
deadline ,
title,
properties ,
olp,
atime,
mtime,
'(' || group_concat(tags, ' ') || ')' as tags,
aliases,
refs
FROM
(
SELECT
id,
file,
filetitle,
\"level\",
todo,
pos,
priority ,
scheduled ,
deadline ,
title,
properties ,
olp,
atime,
mtime,
tags,
'(' || group_concat(aliases, ' ') || ')' as aliases,
refs
FROM
(
SELECT
nodes.id as id,
nodes.file as file,
nodes.\"level\" as \"level\",
nodes.todo as todo,
nodes.pos as pos,
nodes.priority as priority,
nodes.scheduled as scheduled,
nodes.deadline as deadline,
nodes.title as title,
nodes.properties as properties,
nodes.olp as olp,
files.atime as atime,
files.mtime as mtime,
files.title as filetitle,
tags.tag as tags,
aliases.alias as aliases,
'(' || group_concat(RTRIM (refs.\"type\", '\"') || ':' || LTRIM(refs.ref, '\"'), ' ') || ')' as refs
FROM nodes
LEFT JOIN files ON files.file = nodes.file
LEFT JOIN tags ON tags.node_id = nodes.id
LEFT JOIN aliases ON aliases.node_id = nodes.id
LEFT JOIN refs ON refs.node_id = nodes.id
GROUP BY nodes.id, tags.tag, aliases.alias )
GROUP BY id, tags )
GROUP BY id
--order by id, file
"
)))
(mapcan
(lambda (row)
(pcase-let* ((`(,id ,file ,file-title ,level ,todo ,pos ,priority ,scheduled ,deadline
,title ,properties ,olp ,atime ,mtime ,tags ,aliases ,refs)
row)
(all-titles (cons title aliases))
)
(mapcar (lambda (temp-title)
(org-roam-node-create :id id
:file file
:file-title file-title
:file-atime atime
:file-mtime mtime
:level level
:point pos
:todo todo
:priority priority
:scheduled scheduled
:deadline deadline
:title temp-title
:aliases aliases
:properties properties
:olp olp
:tags tags
:refs refs))
all-titles)
))
rows)
)
)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment