Emoji Breakdowns With Raku
Sunday, 14 August, 2022

Customizing a Trilium Report

In which I frolic with a note system that lets me use SQL
Trilium also embeds its scripting as notes, which takes some getting used to.

Been exploring Trilium for notes. One thing I’ll say is the nerdy features are directly accessible to my existing skills.

See, each note system has some way — or a plugin providing some way — to query information about your collected notes. Logseq uses Datascript, which is uh Lisp-ish? Obsidian — if you add the Dataview plugin — has DQL, which is SQL-ish.

Trilium keeps everything in a SQLite database that you can query directly.

Trilium in-app SQL console
Trilium in-app SQL console

My immediate reaction on realizing what I was seeing? Look I don’t usually communicate on this site via memes and image captions, but:

Jurassic Park: “It’s a UNIX system. I know this.”
Jurassic Park: “It’s a UNIX system. I know this.”

I know the gist of SQL. Let’s play with this.

Made a custom template for notes about people. Assigned that template to the appropriate notes. Got curious about how my template-using notes were spread out.

Copied the query from Trilium Demo / Statistics / Note type count and tweaked it a little for my needs.

SELECT
    templateNote.title,
    SUM(
        CASE WHEN note.isDeleted=0 THEN 1 ELSE 0 END
    ) AS countNotDeleted,
    SUM(
        CASE WHEN note.isDeleted=1 THEN 1 ELSE 0 END
    ) AS countDeleted
FROM attributes attr
INNER JOIN notes note
    ON note.noteId = attr.noteId
INNER JOIN notes templateNote
    ON attr.value = templateNote.noteId
WHERE
    attr.name = 'template'
GROUP BY attr.value
ORDER BY countNotDeleted desc
title countNotDeleted countDeleted
Day template 182 18
Person Template 120 0
task template 6 0
Biography template 4 0
Book template 1 0

I duplicated Note type count, pasted my new query, then adjusted the layout JS to reflect the tweaked query.

The result:

Trilium Notes showing pie chart of template usage
Trilium Notes showing pie chart of template usage

The demo graph uses Chart.js, which is fine. I’m already thinking about using Vega instead.

One thing at a time.

Trilium. It’s got SQL. I like that.