Query All The Things (qatt) - Plugin to allow you to query your vault and more with customized rendering

Share & showcase
1

Query All The Things (qatt)

This plugin lets you query your vault like a database using the SQL language. The Results are then rendered via markdown or direct to HTML using the handlebars templating language.

It also allows you to write the results to a separate file for longer term storage and access outside of obsidian.

Documentation can be found at Obsidian - Query All The Things | Obsidian - Query All The Things (sytone.github.io)

You can download the documentation and example vault to open in obsidian to see the queries in action here.

Release Timelines
2023-12-27: v0.9.0 Released

3 Likes
2

Query All The Things (qatt) v0.9.0 Release :tada:

You can download the documentation and example vault to open in obsidian to see the queries in action here.

Features

  • Update markdown table import handling to work with pages with markdown and multiple tables. It will import the first table found on the note.
  • Added do date to task parsing as a column (‘:dash:’, ‘do::’)
  • Added new functions to append to the the task when called from the taskCheckboxWithAppend handlebars helper. Allows done date to be added.
  • Added flexibleTaskCheckbox handlebars helper to allow next status to be specified on click.
  • Added more options to the query export feature. Target pages can be updated once, once a day or once a week.

Bug Fixes and Changes

  • Changes the DOM changes when the code block is parsed to be more consistent.
  • Updated post rending model to be clearer and allow extensions to it.
  • Separated out all handlebars handlers to be separate files, also fixed casing issues.

Documentation

  • Added a TTRPG based example
  • Reordered documentation to be more clear.
  • Created documentation stubs for all handlebars helpers.
  • Added new examples with live versions that work when you open docs as an obsidian vault.

Release 0.9.0 · sytone/obsidian-queryallthethings (github.com)

1 Like
3

Hey, @sytone :slight_smile:

Thank you for the plugin, it’s tickling my curiosity! The docs are a bit sparse, though.

I’d like to know more about querying directories of notes and about joining tables. I do speak SQL, so I don’t mind if you give a technical answer :slight_smile:

I have, for example, a structure like this:

(Vault) -> Periodicals -> Daylies
                             -> Year
                                     -> Month
                             -> Weeklies
                             -> Monthlies
                             -> current_notes

When a month is over, I create a Year folder and below that the month. The daily notes from that month go into the new folder.
So in Periodicals/Daylies are only the notes of the current month.

I’d like to query all notes within all subfolders, preferrably using dataview_pages.

query: |
  SELECT * 
  from dataview_pages
  where file->folder Like "Periodicals/Daylies"

returns only the files under this folder, that is all the files of the current month. If I want to drill down into past years or months, I need to specify the folder right down. How would I work around that?

The next question is about JOIN. Did you implement (left/right) inner or outer JOINs? Would you please show me an example of the syntax for joining tables?

4

I have done some more updates to the documentation and added some more samples to make it clearer.

The like statement needs the wildcards.

query: |
  SELECT * 
  from dataview_pages
  where file->folder Like "Periodicals/Daylies%"

For joins all the ones alasql supports the plugin supports.

Here is an example that shows the count of tasks per page to show completed and total tasks.

query: |
  SELECT TOP 10 
    notes.*, 
    tasks.*, 
    cast((tasks.CompletedTasks/tasks.TotalTasks) * 100 as int) AS perCompleted
  FROM obsidian_markdown_notes AS notes
  JOIN (
    SELECT 
      COUNT(*) AS TotalTasks, 
      COUNT(case when status = 'x' then 1 else null end) AS CompletedTasks, 
      COUNT(case when status <> 'x' then 1 else null end) AS PendingTasks, 
      page 
    FROM obsidian_markdown_tasks 
    GROUP BY page
  ) AS tasks ON notes.path = tasks.page
  ORDER BY perCompleted DESC
template: | 
  {{#each result}}
    - {{basename}} - '{{perCompleted}}%' - {{CompletedTasks}}/{{TotalTasks}}
  {{/each}}
1 Like
5

Query All The Things (qatt) v0.10.0 Release :tada:

You can download the documentation and example vault to open in obsidian to see the queries in action here.

Features

  • Add obsidian handlebars helper and JoinArray SQL function
  • Add settings to disable missing DataView and CustomJS plugins notification
  • Add EXTRACTLINE and LINEINDEX SQL functions
  • Enable processing of DataView Inline fields for use in tasks (obsidian_markdown_tasks table), closes #11

Documentation

  • Multiple updates to the structure and generated documentation to make it more consistent and to enable live examples if the documentation is opened as an Obsidian vault.

Release 0.10.0 · sytone/obsidian-queryallthethings (github.com)

6

Since happening on the thread two months ago, I get notified that there are updates on it.

I was tempted to ask this before but now I am not leaving here without doing so:
What does this plugin do on top of DataView that makes it 1) worthy of installing 2) worthy of learning its (template, whatever) syntax?

This is a genuine question, of course.

1 Like
7

Thanks a lot! That gets me on the horse again :star_struck:

1 Like
8

Try it. You’ll be surprised.

Three of the things that got me hooked: The query language is SQL, so it’s more flexible. You are able to format the results any which way without having to resort to dirty tricks. Finally QATT can replace its own query, leaving you with a plain markdown note. Basejumps from there.

2 Likes
9

Good questions.

Worthyness should be based on the functionality and it helping you manage your information.

The reason I wanted to create this in the first place was to simplify (relative term) exposing data in my vault and how to show it. I was using the tasks plugin and at the time the query language was not as flexible as I wanted. I want to use SQL which is a well know query language to get the data I want. Long story short I made a plugin to test and validate things and as part of that I wanted to show the output in a controllable way. So I used handlebars to render the output. I though other people may benefit from it so published it.

At that point I realized that by itself it could replace a few other plugins I had and make my system more dynamic. So I structured the code block using YAML so the query and template sections would be simple to work with. I created in memory tables based off the dataview plugin to start but ended up making my own cache so now there is a selection of tables built in.

I then wanted to have my own tables and while custome sql could be used to do it I added the ability to import csv, json and markdown tables in a Md file so i can now have a markdown table with birthdays and big event I can join with other queries.

The ability to join tables is pretty powerful. For example you can find all notes with a tag and then all tasks with the same task and show or group by tasks and show counts grouped by the pages they are on. So with that I had pretty much the ability to get or query data I want. As an added bonus it is handy for creating my world for a TTRPG as the pages also form a massive database to query and join.

On the render side I want to just output markdown to allow obsidian or the external micromark processor. Then I realized I could also output html which meant I could use inline css or JavaScript as needed allowing pretty much anything to be rendered.

Then I got bored and enabled the ability to have multiple queries (delimited by ; as per sql language) and the results are now a collection of results allowing me to replace all my dataview custom blocks with JS queries and more complex rendering making it simpler for me to manage.

To make things simple you can reference files for the query or rendering so reuse is simpler and an update in one file is then reflected on all pages using it.

At some point I will write up how I made the dashboard++ approach a few queries so it is easy to manage and dynamic.

I have a guide on how something like meta bind (an awesome plugin) can be used, in that case it was for monster lookup for a TTRPG scenario.

In all the goal was for me to control and render all my data how I want. Also it has the initial feature to render the output to a file so you can see the queries and rendered result outside of obsidian, something other plugins currently do not do so I am not locked in to a application to view my data as I want.

4 Likes
10

Thanks for taking us through the evolution of your plugin.

I have two dashboards with some 20 Dataview queries that give me heads-ups and todos and they are really valuable. Once I carry out the janitor jobs they become empty tables again but what you said about combining tables still sounds great.

One can markdownify DataView tables and that’s what I do to work on my tablet that doesn’t handle a large vault well (especially with DataView related tasks).

I wonder how that’d work on mobile? DataView doesn’t need to be installed and enabled?

11

I no longer have dataview enabled on my phone or desktop and have not noticed issues with performance so far. If I do I would fix it as it would be annoying. :slight_smile:

I did not know dataview can write to separate files, last time I used it I did not see that functionality.

12

Hmmm…interesting…

I’m sure you’ve seen this before:

const query = (`
<DV query comes here>
`);

let out = await dv.queryMarkdown(query);
13

Yes I have seen that. The query block in my plugin can write output once or on a more regular period. For example you can update/create a markdown file every day with all your notes for a project across files and it is written to the file. I use that to generate a snapshot of projects that is a separate file that can be read by anything. Could also be used to create something like a CSV file with query results.

2 Likes
14

Right. Static is handy indeed.

Can your plugin be used to write back to files based on query results as I outlined in this post?

15

At the moment the write is append, prepend or overwrite. There is no way to replace. I have a example in docs where it calls out to a Javascript function from a rendered table to update page properties. I use that for project management.

You can see details on that in the plugin docs. Not quite automatic but gives you lots of control.

16

Like ToolBox Pro (with Siri Shortcuts) on my iPad. So overwrite means overwriting the whole file.

Well, I hope this exchange piqued some curiosity among (prospective) users, mate.
Ta!

2 Likes
17

About 30 minutes into it. Still playing around w/ it to see if it fits my needs but I’m hopeful as a heavy SQL user in a past life.

One potential bug is that, unless it’s a quirk w/ my system, the default for the default post render setting appears to be passing through a NULL setting on first install.

For me at least, I spent the first 15m troubleshooting and then realized that the rendering setting was passing through a blank option that disappears once I make a manual selection

18

I’ll have to look at that. Please make a issue on the repo so I can track.