Can I query all existing values of a given property?

How To Requests
1

I have a custom property called AREA, and I would like to retrieve a list of all exisiting values in the database. I have no idea how to do it with a simple query.

All I would be able to do is query all the nodes (level 0 is enough in that particular case), and process this list to extract AREA unique values using assoc-string I think.

This strategy feels very expensive to me, so I was wondering if it was posible to achieve the same results using org-roam-db-query. I recently installed vulpea, and started experimenting with it, so I would be very happy with an alternative solution using vulpea capabilities.

Thanks for your time.

2

Is this one-off retrieval, or in every Emacs session or command call?

If you need the list frequently, I’d do a query like this below (replace GEO with AREA — for your purpose, you don’t need to retrieve the ID either), dedupe the same occurrences, and keep the resultant list in a simple list variable.

Or I retrieve the list once, and manually create a list variable in init so that you can manually add/remove from it.

LAT and LON are decimal degrees of type number (not string)."
  (let ((nodes (org-roam-db-query
                [:select [properties id]
:from nodes
:where (like properties '"%geo%")])))

source: How to use location in org-roam? - #3 by nobiot

3

I’d dedupe outside the sql, just to be clear, unless you know how in sql (probably UNIQUE can be used…)

4

Thanks for the code.
In my case, every single node in the database has an AREA property, so if I am not mistaken, this snippet would retrieve all my nodes.
To answer your previous question, it is a command call I would not use very often. It would be used in a complete-read prompt when needing to fix metadata and maybe some specific instances of note creation.

5

Then I’d retrieve from every node and keep the deduped list as a variable. I don’t see any issue here. What’s your concern ? In fact, I’d list them manually — I am assuming you want to use sql just to be exhaustive.

6

There is no real issue here, you are right. I should be able to extract the values from nodes, and dedup them. I just wanted to check first if something like SELECT DISTINCT properties.areas FROM nodes was possible, and maybe more performant, or simpler code wise.

I have written a lot of overly complicated Elisp functions over the last few months to find out later I was simply reinventing the wheel (and a lesser version of the wheel on top of it all !). Since I am not very comfortable with org-roam-db-query, I decided to ask if the db could be queried that way.

Anyway, I think it could be possible with the vulpea db extension feature, but I am gonna try the Elisp loop approach first to see how fast (or slow) it is.

Thanks for your help.

7

Did you try this? I thought this was possible — and yes it is DISTINCT not UNIQUE. Will try later at home myself.

For deduping, you can use loop if you like but there is also a dedupe function, delete-dups.

8

I tried without the DISTINCT statement this morning, it returned the litteral value ‘properties.area’ for each node in my database :slight_smile:

I have not been very successful at querying properties in general with org-roam, I forgot about my previous attempts, but I came to the understanding that properties where stored as a ‘stringified’ plist, so I simply started thinking about it differently.

I went back to the vulpea project page a week ago, and my understanding of org-roam and Elisp is finally enough to understand what I read there, and it is a great library. To be honest I thought the author created the meta KEY VAL pairs in part for this reason. There is actually a section about the rationale behind the author’s choice, I do not remember it exactly as I write, so do not take my word for it.

Thanks for the tip, I forgot about that one, but I think I would have found it back. I believe I will still have to loop over the nodes, extract the AREA values into a list, then, dedupe this list. I have not gotten to it seriously yet…

9

OK, I see what you mean now that I’ve tried it myself. The output from org-roam-db-query on properties in the nodes table is not intuitive.

Could I suggest you to try the following code?

This mapcar form should give you a list of all your areas from all your nodes in string, so you will have duplicates in it.

(mapcar
 (lambda (item)
   (cdr (assoc "AREA" (car item))))
 (org-roam-db-query [:select [properties] :from nodes]))

If this works on your end as it does on mine, then it would be a simple operation to dedupe the list, like so:

(delete-dups
 (mapcar
  (lambda (item)
    (cdr (assoc "AREA" (car item))))
  (org-roam-db-query [:select [properties] :from nodes])))

delete-dups uses equal to check duplicate items, so it should work on strings (on my casual test, it works).

10

I just tried it, it works great, it is fast, and the code is clear. I am going to reuse this logic for a few other ideas I wanted to implement.

Thanks for sharing your solution :+1:

1 Like