I decided to learn how to do dynamic blocks in org-mode.
This one lists all the dangling links in the database. it takes an optional parameter to limit the number of links listed.
It can be easily adapted to other uses (look at the gist in raw mode, so you can see how it is used).
For users who have not turned off the auto conversion of roam links to id links – all roam links are dangling links by definition, I have created a query function to query all kinds of back links including roam links with titles or aliases and id links
have a look - this would create a matrix of all kinds of links.
(cl-defun org-roam-link-query-backlinks (id title
&optional
no-id-links
no-roam-title-links
no-roam-aliases-links
&key
aliases)
"Query the SOURCEs where forward links have been defined,
along with the type of links (ID / ROAM).
nth 0 is the SOURCE ID where forward links exist,
nth 1 to 3 are the associated link types in the SOURCE
nth 1 is non-nil if ID links exists in SOURCE
and returns the node ID
nth 2 is non nil if \"roam:TITLE\" links exists in SOURCE
and returns the node TITLE
nth 3 is non nil if \"roam:ALIASES\" links exists in SOURCE
and returns the list of node ALIASES used.
Optional arguments:
When optional NO-ID-LINKS is NON NIL
do not query for ID backlinks
When optional NO-ROAM-TITLE-LINKS is NON NIL
do not query for \"roam:TITLE\" backlinks
When optional NO-ROAM-ALIASES-LINKS is NON NIL
do not query for \"roam:ALIASES\" backlinks.
KEYWORD ARGUMENTS:
- ALIASES: a list of aliases to query for.
"
(org-roam-db-query
(format "
with
alias_links as
(select links.source as source, '(' || group_concat(alias, ' ') || ')' as aliases
from aliases inner join links on aliases.alias = links.dest
where node_id = '\"%s\"' and %s
group by links.source),
title_links as
(select links.source as source, '(' || links.dest || ')' as title_link
from links
where links.dest = '\"%s\"'
group by links.source),
id_links as
(select links.source as source, '(' || links.dest || ')' as id_link
from links
where links.dest = '\"%s\"'
group by links.source)
select source, max(id_link) as id_link, max(title_link) as title_link, max(aliases) as aliases
from
(select source, aliases, null as title_link, null as id_link from alias_links
union all
select source, null as aliases, title_link, null as id_link from title_links
union all
select source, null as aliases, null as title_link, id_link from id_links) as combined_data
group by source;"
(unless no-roam-aliases-links id)
(if aliases (format "links.dest IN (%s)"
(mapconcat (lambda (alias) (format "'\"%s\"'" alias)) aliases ", "))
"links.dest = aliases.alias")
(unless no-roam-title-links title)
(unless no-id-links id))))
Also, to create a report of actual dangling links whether auto conversion is off or not, we should make considerations from the aliases table also
I also want to ask why are you using a Common Table Expression – it is redundant here, makes the code complicated and obfuscated without rationale
Try this
(let ((buffer (generate-new-buffer "*Org-Roam Dangling Links*"))
(query (org-roam-db-query
"select
links.source,
'(' || group_concat(rtrim(links.type, '\"') || ':' || ltrim(links.dest, '\"'), ' | ') || ')'
from links
where links.type in ('\"roam\"', '\"id\"')
and links.dest not in
(select aliases.alias from aliases
union select nodes.id from nodes
union select nodes.title from nodes)
group by links.source;")))
(with-current-buffer buffer
(switch-to-buffer buffer)
(org-mode)
(insert "#+TITLE: Dangling Links Report\n\n")
(insert "* Dangling Links\n\n")
(dolist (row query)
(insert (format "| %s%s | %s\n" "id:" (car row) (cadr row)))
(org-table-align))
(goto-char (point-min))))
Much simpler sql query - creates a buffer dash board of dangling links from where you can manage.
Plus we have to create a new buffer - if we insert using org-babel then the report will be added back as a list of links – which is definitely not what we want.
#+TITLE: Dangling Links Report
* Dangling Links
| id:ids0 | (roam:target111 | id:idtxxx) |
| id:ids1 | (roam:alias44 | id:idtxxx) |
It looks good.
Keep in mind, (format “…%s…” val) is a security vulnerability. As a malicious user can provide something like: “roam\”;delete from files;\“select …” such that the query is still valid.
In this case it is important to make sure that the parameters to use to %s are valid.
Or use a prepared statement.