闪卡 | 做了一个 SQL 执行框的封装，并在内部封装了闪卡的递归处理

简而言之，输入想要查询的 sql（无需考虑其是否具有闪卡），然后点击对应的按钮即可。

前提条件：

• 安装番茄工具箱插件。
• （推荐）查询 Query 挂件。

查询效果展示（是的，没有一炮多响！）：

查询采用递归逻辑如下：

1. 一个 SQL 获得的 block_id【此处是 SQL 执行框所获取的相应 block_id】

2. 针对于 1 获得的 block_id，检查其是否具有自定义属性 custom-riff-decks

   1. 如果是，则取中该 id。
   2. 如果否，追溯其 parent_id，如果其 parent_id 具有自定义属性 custom-riff-decks，则取中该 parent_id

3. 针对于 2，进行循环，当且仅当取中 id，或 parent_id 为空时，结束循环。

代码：

//!js
const query = async () => {
    let dv = Query.DataView(protyle, item, top);
    const sql = dv.useState('sql', '');
    const searchResult = dv.useState('search-result', []);
  
    dv.addmd(`#### SQL RiffCards Executor`);
  
    // SQL输入框
    const textarea = document.createElement('textarea');
    textarea.className = "fn__block b3-text-field";
    textarea.rows = 5;
    textarea.style.fontSize = '20px';
    textarea.value = sql.value;
    dv.addele(textarea);
  
    // 按钮容器（网格布局）
    const btnGrid = document.createElement('div');
    btnGrid.style.display = 'grid';
    btnGrid.style.gridTemplateColumns = 'repeat(2, 1fr)';
    btnGrid.style.gap = '10px';
    btnGrid.style.marginBottom = '10px';
  
    // 创建五个功能按钮（移除分散推迟）
    const addButton = createButton("添加闪卡");
    const removeButton = createButton("移除闪卡");
    const postponeButton = createButton("批量推迟");
    const priorityButton = createButton("调整优先级");
    const refreshButton = createButton("刷新结果");
  
    // 添加到网格（刷新结果放在最后）
    btnGrid.appendChild(addButton);
    btnGrid.appendChild(removeButton);
    btnGrid.appendChild(postponeButton);
    btnGrid.appendChild(priorityButton);
    btnGrid.appendChild(refreshButton);
  
    dv.addele(btnGrid);
  
    // 结果容器
    const resultContainer = document.createElement('div');
    dv.addele(resultContainer);
  
    // 初始渲染结果
    updateResults();
  
    // 按钮事件处理
    const handleButtonClick = async (action) => {
        const blocks = await executeQuery();
        if (!blocks) return;
  
        switch(action) {
            case 'add':
                await tomato_zZmqus5PtYRi.siyuan.addRiffCards(blocks.map(b => b.id));
                break;
            case 'remove':
                await tomato_zZmqus5PtYRi.siyuan.removeRiffCards(blocks.map(b => b.id));
                break;
            case 'postpone':
                const postponeCards = await getCards(blocks);
                await tomato_zZmqus5PtYRi.cardPriorityBox.stopCards(postponeCards, false);
                break;
            case 'priority':
                const priorityCards = await getCards(blocks);
                await tomato_zZmqus5PtYRi.cardPriorityBox.updateDocPriorityBatchDialog(priorityCards);
                break;
            case 'refresh':
                // 仅刷新，不执行额外操作
                break;
        }
  
        updateResults();
        dv.repaint();
    }
  
    // 按钮绑定
    addButton.onclick = () => handleButtonClick('add');
    removeButton.onclick = () => handleButtonClick('remove');
    postponeButton.onclick = () => handleButtonClick('postpone');
    priorityButton.onclick = () => handleButtonClick('priority');
    refreshButton.onclick = () => handleButtonClick('refresh');
  
    // 辅助函数：创建按钮
    function createButton(text) {
        const btn = document.createElement('button');
        btn.className = "b3-button";
        btn.textContent = text;
        btn.style.width = '100%';
        btn.style.padding = '8px 0';
        return btn;
    }
  
    // 辅助函数：递归查找具有属性的父块
    async function recursiveFindParentBlocks(startingBlocks) {
        const MAX_DEPTH = 15;
        const foundBlocks = new Set();
  
        // 递归查找函数
        const findRecursive = async (blockIds, depth = 0) => {
            if (depth >= MAX_DEPTH || blockIds.length === 0) {
                return;
            }
    
            // 检查当前层级的块是否具有目标属性
            const attributeCheckPromises = blockIds.map(async (blockId) => {
                const hasAttribute = await checkBlockHasAttribute(blockId, 'custom-riff-decks');
                return { blockId, hasAttribute };
            });
    
            const attributeResults = await Promise.all(attributeCheckPromises);
    
            // 收集具有属性的块
            const blocksWithAttribute = attributeResults
                .filter(result => result.hasAttribute)
                .map(result => result.blockId);
    
            blocksWithAttribute.forEach(blockId => foundBlocks.add(blockId));
    
            // 获取需要继续向上查找的块（没有找到属性且需要继续查找）
            const blocksToContinue = attributeResults
                .filter(result => !result.hasAttribute)
                .map(result => result.blockId);
    
            if (blocksToContinue.length === 0) {
                return;
            }
    
            // 获取这些块的父块ID
            const parentIds = await getParentBlocks(blocksToContinue);
            const validParentIds = parentIds.filter(id => id !== null && id !== undefined);
    
            if (validParentIds.length > 0) {
                await findRecursive(validParentIds, depth + 1);
            }
        };
  
        // 开始递归查找
        const startingBlockIds = startingBlocks.map(block => block.id);
        await findRecursive(startingBlockIds);
  
        return Array.from(foundBlocks);
    }
  
    // 辅助函数：检查块是否具有特定属性
    async function checkBlockHasAttribute(blockId, attributeName) {
        try {
            const attributeQuery = `SELECT 1 FROM attributes WHERE block_id = '${blockId}' AND name = '${attributeName}' LIMIT 1`;
            const result = await tomato_zZmqus5PtYRi.siyuan.sql(attributeQuery);
            return result && result.length > 0;
        } catch (error) {
            console.error(`检查块 ${blockId} 属性失败:`, error);
            return false;
        }
    }
  
    // 辅助函数：获取块的父块ID
    async function getParentBlocks(blockIds) {
        if (blockIds.length === 0) return [];
  
        const idList = blockIds.map(id => `'${id}'`).join(',');
        const parentQuery = `SELECT parent_id FROM blocks WHERE id IN (${idList}) AND parent_id IS NOT NULL`;
  
        try {
            const result = await tomato_zZmqus5PtYRi.siyuan.sql(parentQuery);
            return result.map(block => block.parent_id).filter(id => id);
        } catch (error) {
            console.error("获取父块失败:", error);
            return [];
        }
    }
  
    // 辅助函数：执行查询（包含递归逻辑）
    async function executeQuery() {
        const sqlText = textarea.value.trim();
        if (!sqlText) {
            searchResult([]);
            return null;
        }
  
        try {
            // 1. 执行原始SQL查询获取起始块
            const startingBlocks = await tomato_zZmqus5PtYRi.siyuan.sql(sqlText);
    
            if (!startingBlocks || startingBlocks.length === 0) {
                searchResult([]);
                return [];
            }
    
            // 2. 递归查找具有目标属性的父块
            const foundBlockIds = await recursiveFindParentBlocks(startingBlocks);
    
            if (foundBlockIds.length === 0) {
                searchResult([]);
                return [];
            }
    
            // 3. 获取完整块信息
            const idList = foundBlockIds.map(id => `'${id}'`).join(',');
            const finalQuery = `SELECT * FROM blocks WHERE id IN (${idList}) LIMIT 999`;
            const finalBlocks = await tomato_zZmqus5PtYRi.siyuan.sql(finalQuery);
    
            sql.value = sqlText;
            searchResult(finalBlocks);
            return finalBlocks;
    
        } catch (error) {
            console.error("查询执行失败:", error);
            return null;
        }
    }
  
    // 辅助函数：获取卡片
    async function getCards(blocks) {
        const cardMap = await tomato_zZmqus5PtYRi.siyuan.getRiffCardsByBlockIDs(blocks.map(b => b.id));
        return [...cardMap.values()].flat();
    }
  
    // 辅助函数：更新结果视图
    function updateResults() {
        resultContainer.innerHTML = '';
        if (searchResult().length > 0) {
            dv.addlist(searchResult(), {
                fullwidth: true,
                cols: null
            }, resultContainer);
        } else {
            const emptyMsg = document.createElement('div');
            emptyMsg.className = "b3-label";
            emptyMsg.textContent = "无查询结果";
            emptyMsg.style.textAlign = 'center';
            emptyMsg.style.padding = '20px';
            emptyMsg.style.opacity = '0.6';
            resultContainer.appendChild(emptyMsg);
        }
    }
  
    dv.render();
}

return query();

鸣谢：

@player 佬

@Frostime 佬

@[DeepSeek - 探索未至之境]