SQL query with JavaScript

Are there any example queries using JavaScript? I saw it is supported in the change log but couldn't find any English documentation. I would be especially interested in dynamically using the id of the document in which the query is embedded within the query.

Thank you!

    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 ...
    • dwinkl 4 Comment
      PRO Author

      Just playing around with this a bit:

      //!js
      const query_path = async (path) => {
      	let notebooks_ = await fetchSyncPost("/api/notebook/lsNotebooks");
      	let notebooks = notebooks_.data.notebooks;
      	// Identify your notebook name:
      	let myNb = notebooks.filter((nb) => nb.name.includes("Notes"));
      	let myId = myNb[0].id;
      	let blocks = await fetchSyncPost("/api/filetree/getIDsByHPath", {path: path, notebook: myId});
      	return blocks.data
      }
      return query_path("/Resources/PC/Python")
      
      
      However, generally queries within the same document don't seem to be possible
      dwinkl
      many thanks for these examples @dwinkl! However, we still don't know how to get the id of the parent block or document, right?
      alvorithm
      @alvorithm that’s right and I think it’s not possible using js or queries alone. It lot looks like they are unaware where they are called from. My next try will be templates which should work at least. They can interpolate the current document id if.
      dwinkl
      @alvorithm Indeed we can query the blocks of the current document with a template
      dwinkl
    • MiscReply
    • dwinkl
      PRO Author

      Thank you for the hint! I did get a minimal example going based on this comment in the GH issue.

      It appears that the //!js blocks want a list of IDs as return value. The simplest possible example is:

      //!js 
      return ["20231229141513-pnebh92"]
      

      However, we can query the API using the following code and return a list of IDs returned by it:

      //!js
      const query = async () => {
      	let blocks = await fetchSyncPost("/api/block/getChildBlocks", {id: "20231229141511-fh9y9hc"});
      	let data = blocks.data;
      	let ids = data.map((d) => {return d.id});
      	return ids
      }
      return query()
      
    • alvorithm
      VIP Warrior

      I asked about it in this thread. So far I've managed to use the document id only when using the query plugin, but then I can't render the blocks like the default embed block sql queries do. I don't know how to use the id from within a normal sql query.

      Presumably, Javascript could help here, but I could not find any documentation about //!js blocks, and I can't get a minimal working example just from the GH issue (translation by Google).

      2 Reply
    • dwinkl 2 Comment
      PRO Author

      The template to query the current document

      {{SELECT * FROM BLOCKS WHERE root_id = '.action{.id}'}}
      
      Thank you, this is useful, if somewhat disappointing. Even just developing a query is cumbersome if it has to be edited outside of SiYuan as a template. It is unclear to me why templates are not editable from within SiYuan.
      alvorithm
      @alvorithm I agree the template creation interface could be better. I haven’t really looked into it and just added the markdown files in the folder. Generally, I prefer using templates for SQL though since it’s a lot of boiler plate anyways. So I just have a template for querying blocks and one that inserts the current document id and then edit the query in Siyuan.
      dwinkl