Query pages via syscall

General
1

I’ve been trying to issue the following query via a state-script function: page where tags = "journal" order by created desc via the datastore.query syscall.

I had a look at the Query type taken by datastore.query and after a couple of iterations, I came up with

```space-script
silverbullet.registerFunction("queryJournalEntries", async (query) => {
  return await syscall("datastore.query", query);
 // I'd like to perform further filtering and aggregation after that
})

```template
{{queryJournalEntries({"select": ["string", "page"], "filter": ["=", ["attr", "tags"], ["string", "journal"]]})}}

This seems to yield results, albeit more than I expected. I wonder if my "select" clause is correct.

Screenshot 2024-02-19 at 19.31.23
Screenshot 2024-02-19 at 19.31.231556×1644 273 KB

On top of that, I couldn’t manage to get the orderBy clause to not cause a ParseError:

```template
{{queryJournalEntries({
  "select": ["string", "page"],
  "filter": ["=", ["attr", "tags"], ["string", "journal"]],
  "orderBy": [{"expr": ["string", "created"], "desc": true}]
  })
}}

Could it be possible to give me an example of a working datastore.query call?

Thank you!

2

You got quite far! While you can probably get this to work , I’d recommend using a call at a slightly higher level of abstraction. This is an API not exposed as a syscall, but as a function on the index plug.

Specifically, queryObjects defined here:

I have no access to a laptop to test and I’m typing this on my phone, but it would look something like: syscall("system.invokeFunction", "index.queryObjects", "journal", {orderBy: [{expr: ["string", "created"], desc: true})

You can narrow it down more in that query object, but querying the “journal” tag directly should get you there without an additional filter.

:crossed_fingers: that this works

3

By the way, why do you want to do this in space script and not just put a query directly in your template, you know you can do this right?

4

Nice, thank you very much! I had to add the eventual missing closing bracket, and it worked like a charm

```space-script
silverbullet.registerFunction("queryJournalEntries", async (tag) => {
  return await syscall(
  "system.invokeFunction", 
  "index.queryObjects", 
  tag, {
  orderBy: [{expr: ["string", "created"], desc: true}]
  })
})

By the way, why do you want to do this in space script and not just put a query directly in your template, you know you can do this right?

I’m looking at implementing a group by, really. I have several pages tagged with

tags: journal
mood: <something>

and I’d like to return the count of journal pages during a time interval, aggregated per mood (as a mood tracker).

The equivalent SQL would be

select count(*)
from page
where page.tags = "journal"
group by page.mood;

Thanks again for your help!

1 Like
5

As a conclusion, here’s how the final implementation works.

I have a list of journal entries with associated mood. I defined a generic groupBy function, as well as the function to query pages by a given tag you helped me with.

```space-script
silverbullet.registerFunction("groupBy", (collection, attribute) => {
  groups = {};
  collection.forEach(item => {
    prop = item[attribute]
    if (groups[prop] === undefined) {
      groups[prop] = 1;
    } else {
      groups[prop] += 1;
    }
  })
  return groups;
})

```space-script
silverbullet.registerFunction("queryJournalEntries", async () => {
  return await syscall("system.invokeFunction", "index.queryObjects", "journal"})

(I actually dropped the orderBy as it gets lost in the aggregation anyway.)

I can then use the following nested function calls to group and count the journal entries by mood:

```template
{{groupBy(queryJournalEntries(), "mood")}}

Screenshot 2024-02-19 at 21.54.42
Screenshot 2024-02-19 at 21.54.421510×222 5.6 KB

2 Likes
6

Cool! I think somehow group by needs to find its way into the query language at some point. Until then I’m happy you can work around it.

1 Like
7

Well, it’s not part of the query language - but one of my space scripts. For the ones who are interested, this is my generic “Group by” replacement function: silverbullet-collection/scripts/CreateView.md at main · gorootde/silverbullet-collection · GitHub

2 Likes