Basic Document and Category Queries

Any queries that consist only of the docs and categories tables.

---

All documents with given category

Documents that have the category 'neorg' and are in the /projects/ folder.

SELECT path, title, description FROM docs d
JOIN categories c ON c.file_id = d.id
WHERE c.name = 'neorg' AND path LIKE '%/projects/%'
ORDER BY created

Format string: - {https://github.com/benlubas/${path:t}}[${title|path:t}] - ${description}

Example output:

- {https://github.com/benlubas/neorg-conceal-wrap}[Neorg Conceal Wrap] - Hard wrap based on concealed width
- {https://github.com/benlubas/neorg-interim-ls}[Neorg Interim LS] - A Language Server in Lua for Neorg
- {https://github.com/benlubas/neorg-query}[Neorg Query] - Database integration for neorg
- {https://github.com/benlubas/neorg-module-tutorial}[Neorg Module Tutorial] - Walking through Neorg's module system with a tangle-able norg file

Count documents and categories

SELECT (SELECT count(*) FROM docs) AS c, (SELECT count(distinct(name)) FROM categories) AS c2

format string: Docs: *${c}*, Categories: *${c2}*

List all categories

SELECT DISTINCT c.name FROM docs d JOIN categories c ON c.file_id = d.id ORDER BY c.name ASC

format string: ${name}

List all categories with alphabetical order for accented letters (in french)

SELECT DISTINCT c.name FROM docs d JOIN categories c ON c.file_id = d.id ORDER BY REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(UPPER(c.name), 'À', 'A'), 'É', 'E'), 'È', 'E'), 'Ê', 'E'), 'Ç', 'C') ASC;|`

format string: ${name}

List notes that belong to several categories

SELECT * FROM docs d
JOIN categories c ON c.file_id = d.id
WHERE c.name IN ("Neorg", "Module", "Query")
GROUP BY d.id
HAVING COUNT(DISTINCT c.name) = 3

format string: {:${path:$}:}[${title|description|path:t}]