Some useful SQL snippets

This post was last updated for 495 days ago, and the information may already be changed

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:

Field Description
id attribute ID
name attribute name, for those custom attribute, there'll be a 'custom-' prefix
value attribute value
type attirbute type
block_id which block this attribute used
root_id Root block ID where this attirbute used, which is the document block ID
box The notebook  in which the  block with this attribute is located
path The path to the document where the content block with this attribute is located

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

image.png

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

    2 Operate
    leolee9086 updated this article at 2023-07-01 11:05:55
    leolee9086 updated this article at 2023-06-30 04:17:24

    Related articles

    Welcome to here!

    Here we can learn from each other how to use SiYuan, give feedback and suggestions, and build SiYuan together.

    Signup About
    Please input reply content ...
    • TaxDevOp
      VIP Warrior

      How can I ask Siyuan for unreferenced blocks? For example, because the document to which the link points has been deleted?

    • MiscReply
    • mka
      PRO

      We need more from this.