Org dynamic blocks: listing dangling links

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
  "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

	      do not query for \"roam:TITLE\" backlinks

	      do not query for \"roam:ALIASES\" backlinks.


- ALIASES: a list of aliases to query for.
   (format  "
      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
  (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 
		     '(' || 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 from nodes
                             union select nodes.title from nodes)
		 group by links.source;")))
    (with-current-buffer buffer
      (switch-to-buffer buffer)
      (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)))
      (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.

Example output:

#+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.