Query node properties at the database level

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

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.

@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.

1 Like