Some useful SQL snippets

table: blocks

This table is used to store content block data.

In the blocks table you can find all base attributes you may want to use in your embed blocks

like these:

docs have children less than 2

select * from blocks where id in (
	select root_id from blocks group by root_id having count(root_id)<=2

) limit 10

select * from blocks：all embed blocks in siyuan should starts with this，which means select all colmuns from table blocks so siyuan can use this data to render embed block content。

root_id：that's the column name in which the owner doc's id stored, so ,you can use this to find every blocks position

having count(root_id)<=2: this defiened the rule used for filet root_id's (in another word ,docs ),only those docs have less than 2 childrens which will be selected

docs created in 2023-04

select * from blocks where created like '202304%'

created : this means when the block created in siyuan (not the creat time of note file),you can use this column to found any block you've created in any time

if only docs you want, use

select *  from blocks where created like '202304%' and type = 'd'

type : the block's content type , siyuan use this to identify how to render the block,you can find nearly all block type in your user guide in siyuan , you can try this : Content Block Type

if everything fine , this link will open user guide in your siyuan , for those links like this , you can try read : Hyperlink

and actually , you can find all blocks table columns and their usage in Database table

table:attributes

This table used to store all blocks' attributes

there're 8 columns in this table:

all the blocks' attribute are there,like name, alias,bookmark,memo,style and all those customed attibute you set use this panel

but you will not find block's create time or updated time in this table , use blocks table instead for this.

all blocks styled

select * from blocks where id in (select block_id from attributes where name = 'style')

select * from blocks :yes , you should always use select * from blocks to start a query in embed block

where id in (......): As the content suggests, thats means find all rows (one table row for one content block ,where there's a block , there's a row in blocks table) where the id can be found in the query result of those sql sentence in the (....)

select block_id from attributes where name = 'style': select all attribute with name 'style',and return there 'block_id' , which means the block use this style