RFC 22 - Queries

[SeriousBug]

This is the discussion for Query support in Dendron.

[hikchoi]

I'm excited about this. I feel like this could become a core building block of many convenient features we can add to Dendron :)

One thing I'm not grasping yet: How is this going to be rendered? Will it be similar to how wildcard note references are rendered?

[SeriousBug]

I'm thinking of a UI where the selected notes titles are listed. For nextjs preview/publishing, each note title will be a dropdown and clicking on it will display it like a reference. For the editor, it would likely have to be just a list of titles where you can click on them to navigate to them.

I think it would be interesting to let users choose how to display these, either inside the query or as a configuration options. Some users might prefer to just render these like ![[foo.*]] wildcard queries where they are all references, and some users might want just a list of titles without the dropdown.

It also would be fun to allow users to format the results into a table where they can choose what to display for each column.

Maybe the queries could be expanded like the extended images ?[[ query ]]{ display: list }, or like ?list[[ query ]] to configure that display. In any case, I'll have to iterate more on that, but I think the RFC is in good condition to start for now.

[hikchoi]

Sounds good. Would be great to see ui components that can take the query results as props and do all sorts of cool stuff :)

[jonathanyeung]

Very cool writeup, very thoughtful!

Some implementation level stuff:

• when would these queries execute? For a published static site, it's pretty trivial, but it could be a bit tricky when working within VS Code. We should be careful that these queries don't cause performance problems.

• Would the query itself execute on the express server side?

• The editor should provide completions, warnings, and preview to provide immediate feedback when writing queries

  How would we actually implement this? Do we have a client side syntax parser? Seems like a tricky proposition.

• I'm not too familiar with Datascript. How portable would the DataScript layer and language syntax be if we decide to later build our note database into a completely different infrastructure/db (for example elastic search to host notes instead of our current filesystem based approach).

• Is ?[[...]] already used by any popular markdown formatting to indicate anything? (question on compat)

• Have you thought about how extensible this system would be if we wanted to query for note content and not just the notes themselves?

Few small stuff on the RFC page:

• Looks like the DataScript link in the Notes section is broken
• The Discussions page is also not linking here.

[SeriousBug]

Thanks for the heads up with the RFC page, fixed those now!

when would these queries execute?

I'm thinking that the queries will have to execute when previewing or editing. You're right that there likely should be some sort of caching mechanism to avoid repeatedly recalculating the query while the user is typing, especially if they are editing something unrelated.

Would the query itself execute on the express server side?

I think that makes the most sense since it will be more reusable (for example queries can be sent in through the CLI too).

How would we actually implement this? Do we have a client side syntax parser? Seems like a tricky proposition.

The engine server can parse the query and send back warnings & highlighting information to be used. I don't think that's too difficult, it's not much different from what the editor does for any other programming language.

How portable would the DataScript layer and language syntax be

Right now I'm planning to simply dump all notes into the DataScript database when we first execute a query, then update the database later if we execute anohter query. DataScript databases are meant to be ephemeral, so this might be good enough in terms of performance, but I'll ultimately have to experiment to see how good it is. Since the DataScript database is kept separate, it's independent of how Dendron stores notes.

Is ?[[...]] already used by any popular markdown formatting

Not that I know of, but hopefully someone will notice if it's conflicting with something!

if we wanted to query for note content and not just the notes themselves?

Not easy. DataScript itself doesn't have fulltext search support. Datomic is sort-of an alternative of DataScript that has fulltext search support, but it's proprietary and I believe is JVM-only.

We either would have to work around this by handling some of the query ourselves (for example run the fulltext search with fuse.js, run the rest of the query with datascript, then combine the results), or we'd have to extend DataScript for fulltext search support. We could also ditch DataScript at that point and build our own query engine. I'm not sure at this point which would be easier.

[jonathanyeung]

Query execute on express server side

Yeah, agree- we should try to push as much logic server side as possible.

Warnings & Highlights

I think the tricky part here is returning warnings that are useful to the user. It's easier to come up with a parser that can look at a valid syntax, but much harder I think to do one for invalid syntax - there are a lot more possibilities of wrong things that the user can do - for example mismatched parentheses, invalid table names, invalid field names, etc. Can we actually point to the line/column number of the problem? I know a lot of SQL IDE's still are not good at this.

Re: Client side vs Server side checking: I think how some SQL IDE's do it is they actually do a hybrid approach. If there are some easy things that can be highlighted, i.e. misspelling of a table name, then it can handle it client side, but if it's something harder to detect, it'll only return that feedback after the user submits for execution. Perhaps the harder part of our situation is that there's no 'execute query' button that the user clicks, especially if this is embedded into a note. We'd probably need to set a high debounce timer for this.

I think overall - it's probably not worth investing too much in this aspect of the feature. If we can just get some very basic feedback to the user, that's probably sufficient for now.

Portability of DataScript layer

Approach sounds good - we can't know until we run it. This approach will probably hit scalability problems at some point, but if it works well for our current Dendron org-workspace, it's probably good enough for all users for now.

Of course there are a lot of optimizations we can potentially do, but we can avoid doing them until we have to.

Querying of Note Content

I feel like at some point what we'll want to do is to store notes (when we have an organization using Dendron at a large scale) in a more scalable DB technology itself, in which case we can then leverage that DB's query framework, for example elastic search. The more important and relevant piece now is to try to build up the right abstractions such that subbing in a different DB technology as the backend can be seamless as possible. Right now, I don't think much of the surrounding, existing code base (outside of this queries feature I mean) would allow it to be seamless, but it's something to keep in mind.

[kevinslin]

as much as makes sense, we should adopt similar syntax from logseq/roam to maximize functionality and make it easier to convert from one to the other in pods in the future

[matthewbellringer]

This is a great addition to Dendron that I very much support. There are two details that would support my use-cases for this:

• Control over sort-order in the query; for example, all "tags.todo" sorted by a metadata value of "due date".
• The ability to use queries to produce page references in other pages. Indeed, the entire references system would become extremely powerful if this were the mechanism used to create them.

I have no particular preferences to syntax, though I do agree with other posters that we're approaching database territory. My personal preference would be "one I know already", and the most powerful/flexible of those is probably GraphQL based upon the schemata defined in the vault. That also intersects nicely with the graph visualisation tools which Dendron offers, so that people could explore their queries in a more visual way.

[whatamithinking]

Is there any movement on this feature? It would be great to have "smart" lists of tasks using this to deal with different contexts without lots of manual work.

[SeriousBug]

It is still planned, but we are not prioritizing it at the moment. I hope to start working on it within this year.

We are open to community contributions if someone wanted to implement it.

[KucharczykL]

Hello, the link in the OP (https://wiki.dendron.so/notes/O4f9yfDoO7E7gRRDeBeCh.html) is broken.

[SeriousBug]

Sorry, just updated the link at the top.

[ScriptAutomate]

Related issue:

• Advanced Queries #159

[namjul]

How would we actually implement this? Do we have a client side syntax parser? Seems like a tricky proposition.

I have been playing with https://github.com/TryGhost/NQL/tree/main/packages/nql in a sideproject which is used and build by Ghost for https://ghost.org/docs/content-api/#filtering
They have build their parser using https://github.com/zaach/jison. Maybe an option for parsing queries.

[gwenwindflower]

Throwing this out there, I could be just thinking crazy from the amount of pie I ate yesterday (🦃 commenting the day after Thanksgiving in the US), but if there's already an MVP of using SQLite for the metadata backend, is this potentially something that could be handled more holistically by enabling code execution cells a la Orgmode, Sniprun to create a literate programming environment, potentially leveraging the notebook features in VSCode for cells and output (or not and just using triple backtick blocks)?

I strongly believe not using one of the wacky query languages that other PKMs have used, and instead using the standard for query languages, SQL, specifically the SQL flavor already baked into SQLite, would be far more ideal.

With the above you'd feed two birds with one scone, if you will, literate programming/code execution and queries. You'd just make a block and write a SQL query inside it.

Again, I ate a ton of pie 🥧 yesterday and it perhaps has entered my brain 🧠, but this (code execution) feels like a feature on the same level of utility and difficultly as creating a query system, but it could accomplish both.

Regardless of approach, I'm a data engineer and developer experience advocate professionally, so data tooling is what I live and breathe, and I'm deeply interested in helping to implement this. Would love to talk more about how I can get involved! ❤️

[SR--]

I agree about using SQL to formulate queries. I used that with Biblioscape back in the day and it was convenient.