Query pages via syscall

brouberol · February 19, 2024, 6:23pm

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.

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!

zef · February 19, 2024, 8:08pm

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:

github.com

silverbulletmd/silverbullet/blob/main/plugs/index/api.ts

import { datastore } from "$sb/syscalls.ts";
import {
  KV,
  KvKey,
  KvQuery,
  ObjectQuery,
  ObjectValue,
} from "../../type/types.ts";
import { QueryProviderEvent } from "$type/types.ts";
import { builtins } from "./builtins.ts";
import { determineType } from "./attributes.ts";
import { ttlCache } from "$lib/memory_cache.ts";

const indexKey = "idx";
const pageKey = "ridx";

/*
 * Key namespace:
 * [indexKey, type, ...key, page] -> value
 * [pageKey, page, ...key] -> true // for fast page clearing

This file has been truncated. show original

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.

that this works

zef · February 19, 2024, 8:10pm

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?

brouberol · February 19, 2024, 8:41pm

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!

brouberol · February 19, 2024, 8:56pm

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")}}

zef · February 19, 2024, 9:01pm

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.

gorootde · July 17, 2024, 2:47pm

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