How can I query the database using variables?

How To
1

I am trying to query the database from within a function using variables.
In my tests, I tried gathering all files which have a property named “TYPE” set to the value “how to”.

I could not find exhaustive documentation about org-roam-db-query, and I usually use the org-roam-ql to do queries. But I was getting errors too using variables, so I figured I should remove the middle man to try and solve that problem.

Thanks to a few examples here and there, I can retrieve a list of 40 files using the code below:

(defun my/test ()
  (let (
         (files   
          (org-roam-db-query
           [
            :select [file]
            :from nodes
            :where (like properties '"%TYPE%how to%")
            ])))
    (length files)))

(my/test "how to")

I will spare you all my other attempts, the best I could do was writing the follwing function which returns 0 instead of 40, but has at least the merit of not throwing a stringp type error:

(defun my/test (type)
  (let* (
         (prop (concat "%TYPE%" (format "%s" type) "%"))
         (files
          (org-roam-db-query
           [
            :select [file]
            :from nodes
            :where (like properties (quote prop))
            ]
           )))
   (length files)))

(my/test "how to")

I would really appreciate some help because I do have a few functions depending on that kind of logic, and I am really stuck (and I have not even started trying to query using tags!).

2

Do the following:

  • create the entire query as a string, rather than the recommended way
  • doing so requires to escape % and "
  • to escape % double it
  • to escape ", backslash it

for example: this query retrieves the properites of nodes with the category “faq_git”

 (org-roam-db-query "select properties
                     from nodes
                     where properties LIKE '%%\"CATEGORY\" . \"faq_git\"%%'
                     ")

and once you know the syntax, you can use the typical manner, escaping the characters as necessary:

 (org-roam-db-query [:select [properties]
                     :from nodes
                     :where (like properties $r1 )
                     ]  "%%\"CATEGORY\" . \"faq_git\"%%")

so the key seems to be to properly escape the % and " in the string.

It appers that you need a string st such that (format st) will return the string (without single quotes) that SQL expects.

#+begin_src emacs-lisp   :exports both
(format "select properties
                     from nodes
                     where properties LIKE '%%\"CATEGORY\" . \"faq_git\"%%'
                     ")
#+end_src

 #+RESULTS:
 #+begin_example
 select properties
                          from nodes
                          where properties LIKE '%"CATEGORY" . "faq_git"%'
                          
 #+end_example

This also seems to work (using quote, only quoting ")

 (org-roam-db-query [:select [properties]
                     :from nodes
                     :where (like properties $r1 )
                     ]  (quote "%\"CATEGORY\" . \"faq_git\"%"))
1 Like
3

Thank you for all the details, that was very helpful.

I still have a lot to understand about the syntax before being comfrotable querying the database, but that’s progress :+1:

4

org-roam-ql author here, if you need help with using the package, don’t hesitate to reach out.

5

quick question. Is there a way to call elisp functions in sqlite (as user-defined functions, similar to the way it can be done in python using conn.create_function (see create_function in sqlite3 — DB-API 2.0 interface for SQLite databases — Python 3.12.5 documentation)

6

Thanks, I did actually, and, backquotes easily solved the problem with org-roam-ql. When using org-roam-db-query I tend to go for the (format "%s" sql-string) solution from @dmg answer.

1 Like
7

As far as I know, the emacsql package doesn’t have that functionality. My understanding is it requires a pointer to a routine sqlite can call, and I am not sure how to even go about that. You may want to ask the authors there to see if it possible.