How to qualify a column name in a SQL query passed to emacsql?

Troubleshooting
1

I also posted this question on the Emacs StackExchange: sqlite - How to qualify a column name in a SQL query passed to emacsql? - Emacs Stack Exchange

Here’s the question in its entirety:

Some context

I’m querying my org-roam database, which is using the default sqlite3 format.

The code I expected to work

When I pass the following query to emacsql:

[:select [source title nodes.properties]
         :from links
         :inner-join nodes :on (= source id)
         :where (= dest $s1)]

the source and title columns contain what I expect, but the properties column merely contains the symbol nodes.properties, rather than the content of the properties column from nodes.

Further explorations

I can even replace nodes.properties with nodes.sauerkraut – a column that does not exist – and similarly, the third element of each row of the response will just be the symbole nodes.sauerkraut.

If I remove the qualification, putting sauerkraut rather than nodes.sauerkraut, I get a reasonable error:

Debugger entered--Lisp error: (emacsql-error nil nil nil ("SQL logic error" "no such column: sauerkraut" 1 1))
2

Use “:”.
This seems to work on my end – I get a result of the query.

(org-roam-db-query
 [:select [source title nodes:properties]
          :from links
          :inner-join nodes :on (= source id)
          :where (= dest $s1)] "2021-01-16T121946")
3

Alternatively, as you seem to be familiar with SQL, you can use the normal SQL statements by passing them as a string. See how the function org-roam-node-list does this.

4

Works for me too! Woohoo! Thanks again (for the tenth time?), @nobiot!

1 Like