I’d like to have a list of new connections made between nodes in org-roam. (Similar to the example here: Patterns — Gordon Brander)
I can query org-roam.db for:
select n1.id, n1.title, n2.id, n2.title
from links
inner join nodes as n1 on links.source = n1.id
inner join nodes as n2 on links.dest n2.id
where type = '"id"'
order by links.rowid desc
limit 42
but the rowid is not helpful for this - you can’t rely on it being in order of links added.
Has anyone tried to achieve this and had any success? I wonder if I would need to patch the code to have either a unique id in the links table, or a created_at field or similar.
I suspect that you’d need to patch the code to do something extra.
The link table in org-roam-db has properties field but it’s a text field, which you may struggle to do SQL-level sorting and other operations for dates.
I’d consider creating my own custom table that stores the create_at field with a date or integer data type, and adapt the code in org-roam-db to populate it when I newly created a link (and take care of the delete…, but ignore updates).
Or… add a date field to the org-roam-db’s links table… Not sure which is easier.
But then I realise that org-roam.db is only a cache layer over the data in the org files (right?). And could be rebuilt at any time, losing any externally added datetimes. So if I wished to store a created_at field for links then really I must also have it stored somewhere in the org files themselves. I think.
Which doesn’t seem particularly feasible. I would need to be able to annotate links - a bit like in Add Link-tags feature.
Oh, yes, I forgot about this. You are right. I have almost never had to rebuild the db file and don’t sync my notes with other machines. If you also sync notes between machines, the created_at would be different too (!).
This means… You’d probably have to extend the Org’s ID link syntax so that you can represent the timestamp directly in the link itself – as extensively discussed in the “link-tag” thread…