简体中文 | English

widget-query

A widget that renders the query results of the Siyuan Notes database in tabular style.

It is now on the shelves of the Siyuan Notes Community Bazaar. If you like this widget, welcome to light up ⭐ for this project!

PREVIEW

Background color status indication:

White:
- Initialization.
- Processing query.
Green:
- Query success.
Blue:
- Please continue to operate.
Yellow:
- Query result is empty.
Red:
- SQL SQL statement error.
- Unknown error.

FUNCTION

Click the Auto query check box to automatically query once the next time you open the page.
Set custom attribute input for a widget block can specify the content of a SQL code block or an embedded block as a query statement.
- For example, in other documents there is a code block or an embedded block ID which ID is 20220418210605-ibussa1, then set the custom block attribute input: 20220418210605-ibussa1 for the widget block can reference the block's SQL statement for query.
- Only if the pre-block of the widget block is not a code block with the custom attribute type: query-code.
Set custom attribute output for a widget block can specify a table block as a display block for query results.
- For example, in other documents there is a table block ID which ID is 20220604112815-sfiwyi7, then set the custom block attribute output: 20220604112815-sfiwyi7 for the widget block can render the query results into the table.
- Only if the post-block of the widget block is not a table block with the custom attribute type: query-table.

SQL statements that conform to the regular expression ^\s*SELECT\s+\*\s+FROM\s+blocks\s+.* will enable the default block query mode.

This regular expression is configured in config.query.regs.blocks in /src/script/module/config.js
Example: SELECT * FROM blocks WHERE content LIKE '%Content block%'

In this mode, the query result rendering style is controlled with the following configuration options.

Field	Field Description	Field Value	Field Value Description
`config.query.render.*`	Define the rendering style of certain fields. Hyperlink style is like `[anchor text](siyuan://blocks/block)`, which will not be displayed in the backlink panel. Block reference style is like `((block "anchor text"))`, which will be displayed in the backlink panel.	`'link'` `'ref'`	Render the field in hyperlink style. Render the field in block reference style.
`config.query.render.ial.shape`	Define the arrangement method of the block attribute list `IAL`. Display in rows within a column. Display in columns within a row.	`'rows'` `'columns'`
`config.query.render.ial.fields.forced`	Define the forced rendering attribute names of `IAL`. If the array is empty, use the blacklist/whitelist to control.	`['attribute name', ...]`
`config.query.render.ial.fields.ignore`	Define the attribute names of `IAL` that should not be rendered (blacklist). Higher priority than whitelist.	`['attribute name', ...]`
`config.query.render.ial.fields.ignore`	Define the attribute names of `IAL` that can be rendered (whitelist). If empty, render all fields except for those on the blacklist.	`['attribute name', ...]`
`config.query.limit`	Define the display style of the query results for the `content` or `markdown` fields.	`null` `'row'` `'len'`	No limit. Limit by number of rows. Limit by length.
`config.query.maxlen`	Define the maximum length of the query result for the `content` or `markdown` field.	Positive integer.	Enabled when `config.query.limit: 'len'` is set.
`config.query.maxrow`	Define the maximum number of rows of the query result for the `content` or `markdown` field.	Positive integer.	Enabled when `config.query.limit: 'row'` is set.
`config.query.fields`	Define the fields to be displayed in the query results and their order of arrangement.	`['field name', ...]`	See details for field names in blocks
`config.query.style.table.attributes`	Define the block attributes of the query result table, which can be used to set custom styles.	`[{enable: true/false, key: 'block attribute name', value: 'block attribute value'}, ...]`
`config.query.style.column.*`	Define the style of a column in the query result table.	`{: style="width: 512px"}`	Specify the width of a column in the query result.
`config.query.style.align.*`	Define the alignment of a column in the query result table.	`:-` `:-:` `-:`	Left-align a column. Center-align a column. Right-align a column.
`config.query.filter.blocks`	Define a filter sequence to filter out some query results.	`{enable: true/flase, handlers: [(row, data) => {}, ...]}`	Return `true` for items that need to be filtered out; return `false` for items that need to be retained.
`config.query.handler.*`	Define the processing function of a specific field value.	`(row, ial) => {}`	`row`: current record. `ial`: the inline attribute list parsed from the current query record.
`config.query.map.*`	Query result mapping table, replace some query result field values with human-readable fields.

Queries that do not conform to the default block query mode are normal query mode

Example:

Query the help document 请从这里开始 and all of its subordinate documents.

SELECT
    '[' || b.content || '](siyuan://blocks/' || b.id || ')' AS __1____pre__文档标题,
    b.hpath AS __2__文档路径
FROM
    blocks AS b
WHERE
    b.type = 'd'
    AND b.hpath LIKE '%请从这里开始%'
ORDER BY
    b.path
LIMIT 10

	Document Title	Document Path
1	Getting Started	`/Getting Started`
2	Editor	`/Getting Started/Editor`
3	Layout Elements	`/Getting Started/Editor/Layout Elements`
4	Content Blocks	`/Getting Started/Content Blocks`
5	What are Content Blocks?	`/Getting Started/Content Blocks/What are Content Blocks?`
6	Referencing Content Blocks	`/Getting Started/Content Blocks/Referencing Content Blocks`
7	Navigating within Content Blocks	`/Getting Started/Content Blocks/Navigating within Content Blocks`
8	Content Block Types	`/Getting Started/Content Blocks/Content Block Types`
9	Embedding Content Blocks	`/Getting Started/Content Blocks/Embedding Content Blocks`
10	Converting between Document Blocks and Title Blocks	`/Getting Started/Content Blocks/Converting between Document Blocks and Title Blocks`

Query all tables in Siyuan database.

SELECT
    *
FROM
    sqlite_master

	name	rootpage	sql	tbl_name	type
1	`stat`	`2`	`CREATE TABLE stat (key, value)`	`stat`	`table`
2	`blocks`	`3`	`CREATE TABLE blocks (id, parent_id, root_id, hash, box, path, hpath, name, alias, memo, content, markdown, length, type, subtype, ial, sort, created, updated)`	`blocks`	`table`
3	`spans`	`4`	`CREATE TABLE spans (id, block_id, root_id, box, path, content, markdown, type, ial)`	`spans`	`table`
4	`assets`	`5`	`CREATE TABLE assets (id, block_id, root_id, box, docpath, path, name, title, hash)`	`assets`	`table`
5	`attributes`	`6`	`CREATE TABLE attributes (id, name, value, type, block_id, root_id, box, path)`	`attributes`	`table`
6	`refs`	`7`	`CREATE TABLE refs (id, def_block_id, def_block_parent_id, def_block_root_id, def_block_path, block_id, root_id, box, path, content, markdown, type)`	`refs`	`table`
7	`file_annotation_refs`	`8`	`CREATE TABLE file_annotation_refs (id, file_path, annotation_id, block_id, root_id, box, path, content, type)`	`file_annotation_refs`	`table`

Query all fields in the blocks table of Siyuan database.

PRAGMA table_info('blocks')

	cid	name
1		`id`
2	`1`	`parent_id`
3	`2`	`root_id`
4	`3`	`hash`
5	`4`	`box`
6	`5`	`path`
7	`6`	`hpath`
8	`7`	`name`
9	`8`	`alias`
10	`9`	`memo`
11	`10`	`content`
12	`11`	`markdown`
13	`12`	`length`
14	`13`	`type`
15	`14`	`subtype`
16	`15`	`ial`
17	`16`	`sort`
18	`17`	`created`
19	`18`	`updated`

Customized property views.

SELECT
    '[' || b.content || '](siyuan://blocks/' || a.block_id || ')' AS __1____pre__Title,
    MAX(
        CASE
            WHEN a.name = 'name' THEN a.value
            ELSE NULL
        END
    ) AS __2____kbd__Name,
    MAX(
        CASE
            WHEN a.name = 'alias' THEN REPLACE(
                '<kbd>' || a.value || '</kbd>',
                ',',
                '</kbd><br/><kbd>'
            )
            ELSE NULL
        END
    ) AS __3____pre__Alias,
    MAX(
        CASE
            WHEN a.name = 'memo' THEN REPLACE(
                '<kbd>' || a.value || '</kbd>',
                ',',
                '</kbd><br/><kbd>'
            )
            ELSE NULL
        END
    ) AS __3____pre__Memo
FROM
    attributes AS a
    INNER JOIN blocks AS b ON a.block_id = b.id
WHERE
    (
        a.name = 'name'
        OR a.name = 'alias'
        OR a.name = 'memo'
    )
    AND b.type = 'd'
GROUP BY
    a.block_id
ORDER BY
    RANDOM()
LIMIT
    10;

Use field aliases prefix to define query display styles.
- __hidden__alias0:
  - This field does not appear in the query results.
- __ref__alias1:
  - The field is rendered as a block reference.
  - example: ((<value> "<value>"))
- __link__alias2:
  - The field is rendered as a block link.
  - example: [<value>](siyuan://blocks/<value>)
- __raw__alias3:
  - The field is rendered as the original value (inline code style).
  - example: <value>
- __date__alias4:
  - The field is rendered as a date.
  - example: yyyy-MM-dd
- __time__alias5:
  - The field is rendered as a time.
  - example: HH:mm:ss
- __datetime__alias6:
  - The field is rendered as a datetime.
  - example: yyyy-MM-dd HH:mm:ss
- ___s__alias7:
  - The field is rendered as strikethrough.
  - example: ~~<value>~~
- ___u__alias8:
  - The field is rendered as underline.
  - example: <u><value></u>
- ___em__alias9:
  - The field is rendered as emphasis.
  - example: *<value>*
- __tag__alias10:
  - The field is rendered as tag.
  - example: <kbd><value></kbd>
- __kbd__alias11:
  - The field is rendered as keyboard.
  - example: ~<value>~
- __sub__alias12:
  - The field is rendered as subscript.
  - example: ^<value>^
- __sup__alias13:
  - The field is rendered as superscript.
  - example: #<value>#
- __code__alias14:
  - The field is rendered as inline code.
  - example: <value>
- __mark__alias15:
  - The field is rendered as mark.
  - example: ==<value>==
- __math__alias16:
  - The field is rendered as math formula.
  - example: $<value>$
- __strong__alias17:
  - The field is rendered as weightbold.
  - example: **<value>**
- __pre__alias7:
  - The field is rendered as a preview (rendering the markdown row-level identifier).
  - example: <value>
Use field aliases prefix to define query result field order.
- __<number>__alias8:
  - The field can be placed in front of the query style prefix field.
  - example:
    - __1____pre__alias9
    - __02____raw__alias10
Displays the original value of the query result by default (using inline code).

Example of a field alias prefix

SELECT
    b.id AS __00____ref__ref,
    b.id AS __01____link__link,
    b.id AS __02____pre__pre,
    b.id AS __03____raw__raw,
    b.created AS __04____date__date,
    b.created AS __05____time__time,
    b.created AS __06____datetime__datetime,
    b.id AS __07____s__s,
    b.id AS __08____u__u,
    b.id AS __09____em__em,
    b.id AS __10____tag__tag,
    b.id AS __11____kbd__kbd,
    b.id AS __12____sub__sub,
    b.id AS __13____sup__sup,
    b.id AS __14____code__code,
    b.id AS __15____mark__mark,
    b.id AS __16____math__math,
    b.id AS __17____strong__strong
FROM
    blocks AS b
WHERE
    id = '.root{.id}'

Partial template field parsing support
- .<prefix>{.<field>}
  - <prefix>: prefix field
    - block: Widget block.
    - parent: The parent block of the pendant block.
    - root: The document block in which the pendant block resides.
  - <field>: attribute field
    - The field name of the blocks table in the database.
  - Example: SELECT * FROM blocks WHERE id = '.root{.id}' content LIKE '%content blocks%'
    - Equivalent to SELECT * FROM blocks WHERE id = '.block{.root_id}' content LIKE '%content blocks%'
    - Query all blocks in the document where the pendant is located that contain the word content blocks

CUSTOM CONFIG

GLOBAL CUSTOM CONFIG

Create a file <workspace>/data/widgets/custom.js
The value defined in file <workspace>/data/widgets/custom.js overwrites the corresponding value in file <workspace>/data/widgets/Query/src/script/module/config.js.

CONFIG EXAMPLE

/**
 * File Path
 *    <workspace>/data/widgets/custom.js
 * Example function:
 *    Block attributes are displayed in columns in the default block query results.
 */

export const config = {
    query: {
        render: {
            ial: {
                shape: 'columns',
            },
        },
    },
};

For more configuration items, see config.js

BLOCK CUSTOM CONFIG

Set custom block attributes in the widget block(the Query button in your note).
- Custom block attribute names are property in the config object under the file src/script/module/config.js.
- For example, if you want to customize the query result field list, you can set the custom block attribute query-fields: ["hpath", "type", "markdown"], where the config.query.fields property will be replaced in this block.

START

The widget has been put on the shelves at SiYuan community bazaar and can be installed directly in the Bazaar.

REFERENCE & THANKS

Author	Project	License
leolee9086	leolee9086/cc-baselib	Unknown

PS: Sort in no particular order.

CONTRIBUTORS

_leolee9086 🐛 💻	_jpanda-cn 💻 🤔	_{Wang Yong} 📖	_banjuer 🤔	_Tlonglan 🤔	_{Tim Zhang} 🤔 💻	_lmmxj 🤔
_Frostime 💻

PS: The table is generated automatically using All Contributors · GitHub, go to emoji key to see the contribution type.

widget-query
widget-query copied to clipboard

Metadata

widget-query

PREVIEW

FUNCTION

CUSTOM CONFIG

GLOBAL CUSTOM CONFIG

CONFIG EXAMPLE

BLOCK CUSTOM CONFIG

START

REFERENCE & THANKS

CONTRIBUTORS

CHANGE LOG

← Metadata

Owner

Metadata

widget-query widget-query copied to clipboard

Metadata

widget-query

PREVIEW

FUNCTION

CUSTOM CONFIG

GLOBAL CUSTOM CONFIG

CONFIG EXAMPLE

BLOCK CUSTOM CONFIG

START

REFERENCE & THANKS

CONTRIBUTORS

CHANGE LOG

← Metadata

Owner

Metadata

widget-query
widget-query copied to clipboard