Query node properties at the database level

How To
1

Table of Contents

  1. What I’m trying to accomplish
  2. Current workaround
  3. The question

What I’m trying to accomplish

Hello, everyone. I’m currently using Org Roam for storing articles that I want to publish in my blog.

Every article is stored using the following format (see two examples below)

:PROPERTIES:
:ID: c0f2f094-f412-4451-9765-3aa06f7c0db4
:STATUS: Completed
:END:

#+TITLE: Export Org Mode file to PDF using LaTeX

:PROPERTIES:
:ID: c5fa12c9-10b5-419b-8adf-1669365ff266
:STATUS: Published
:END:

#+TITLE: Table formulas in Org Mode

As you can see above, each article has a STATUS property.

What I’m trying to accomplish is to list all the articles whose STATUS is Published.

Current workaround

I have managed to do this using dolist. However, I don’t find this approach efficient because I have to load all the nodes in memory and, in addition to that, I need to iterate through all nodes linearly. I’m mentioning this so that people don’t suggest me doing this.

(dolist (node (org-roam-db-query [:select [title properties] :from nodes]))
  (catch 'continue
    (let (title properties)
      (setq title (nth 0 node))
      (setq properties (nth 1 node))
      (unless (and (assoc "STATUS" properties)
                   (equal (cdr (assoc "STATUS" properties)) "Completed"))
        (throw 'continue t))
      (message "%s" title))))

Instead, I want to directly query the database since that way we can take advantage of the algorithms that are implemented at the database level instead of using dolist (linear search).

(org-roam-db-query
 [:select title
          :from nodes
          :where <<there exists a property with name "STATUS" and value equal to "Completed"])

The question

Is it possible to query properties at the database level?

Any help is appreciated.

1 Like
2

I don’t believe the Org Roam database keeps track of all heading properties. Maybe instead you want something like org-ql?

But if it were me, I’d make custom TODO states and then just have agenda views that show everything.

3

@rdrg109

Just to show there is a possibility; at least this below works on my end.
I am hoping that there should be a better way, to specify a single property in the vector. I need to leave it to someone with SQL expertise.

(org-roam-db-query
 [:select [title properties]
	  :from nodes
	  :where (like properties '"%STATUS%Completed%")])

@jonathan , org-ql might be a good idea. Org-roam cache database keeps file/heading properties in tables such as nodes in the column named properties. You can see this by looking at the variable org-roam-db--table-schemata.

2 Likes
4

I was just poking around trying to see if I could pull some kind of stats about the rate of addition to my org-roam.

I have one file in my roam that starts with:

:PROPERTIES:
:ID:       ad91b5cc-bdc0-46ed-a393-8db45707c194
:CREATED:  [2021-09-08 Wed 12:20]
:END:
#+title: KDE

And I tried running this elisp:

(org-roam-db-query
 [:select [title properties]
	  :from nodes
	  :where (like properties '"%CREATED%2021%")])

After several minutes it has yet to return. My database currently has 11308 nodes across 3288 files.

Am I doing something wrong here or is there a better way to pull some stats like number of nodes created in some time period?

5

I don’t know of a better way. I’d try the same cod with a very small number of nodes in the db just as a test.

6

For what it’s worth, I left that running and walked away, came back hours later and it had returned at some point, with many results, my buffer size had grown to 1.8M.

7

Here we go,

Perhaps it helped to first run (setq org-roam-db-gc-threshold most-positive-fixnum).

But also outputting to a file execution time is much much faster. Anyway, Yes, I was able to query with org-roam-db-query on properties like CREATED.

   #+begin_src emacs-lisp :tangle no
     (org-roam-db-query
      [:select [file]
         :from nodes
         :where (like properties '"%CREATED%2023%")
         :limit 1
         ]
     )
   #+end_src

   #+RESULTS:
   | /home/nickanderson/org/roam/daily/work/2021-06-08.org |