Knex QueryBuilder：动态为现有查询添加Schema的策略

const knex = require("knex")({ client: "mysql" }); // 示例使用MySQL客户端
<p>// 1. 构建带有Schema占位符的基础查询
// 注意：占位符 '#.' 被包含在反引号中，以匹配Knex对MySQL标识符的引用方式。
const readOnlyQuery = knex
.select("*")
.from("#.users as u")
.leftJoin("#.pets as p", "u.id", "p.idUser")
.where("u.id", 1);</p><p>// 2. 冻结原始查询对象，防止意外修改（推荐做法）
Object.freeze(readOnlyQuery);</p><p>/**</p>
                    <div class="aritcle_card">
                        <a class="aritcle_card_img" href="/ai/1567">
                            <img src="https://img.php.cn/upload/ai_manual/000/000/000/175680254776358.jpg" alt="Codeium">
                        </a>
                        <div class="aritcle_card_info">
                            <a href="/ai/1567">Codeium</a>
                            <p>一个免费的AI代码自动完成和搜索工具</p>
                            <div class="">
                                <img src="/static/images/card_xiazai.png" alt="Codeium">
                                <span>228</span>
                            </div>
                        </div>
                        <a href="/ai/1567" class="aritcle_card_btn">
                            <span>查看详情</span>
                            <img src="/static/images/cardxiayige-3.png" alt="Codeium">
                        </a>
                    </div>
                <ul><li>返回一个新的Knex QueryBuilder对象，其中包含给定Schema的查询。</li><li>@param {object} queryBuilder - 原始的Knex QueryBuilder对象。</li><li>@param {string} schema - 要注入的数据库Schema名称。</li><li>@returns {object} 包含指定Schema的新Knex原始查询。
*/
function buildQueryWithSchema(queryBuilder, schema) {
// 3. 将QueryBuilder转换为SQL字符串并替换占位符
// 4. 通过knex.raw()生成新的查询
return knex.raw(
queryBuilder.toString().replaceAll("#", "" + schema + "")
);
}</li></ul><p>// 为不同的Schema生成查询
const queryBuilderSchemaPublic = buildQueryWithSchema(readOnlyQuery, "public");
console.log("Public Schema Query:", queryBuilderSchemaPublic.toString());</p><p>const queryBuilderSchemaPrivate = buildQueryWithSchema(readOnlyQuery, "private");
console.log("Private Schema Query:", queryBuilderSchemaPrivate.toString());</p><p>// 实际执行查询的示例（需要配置数据库连接）
// queryBuilderSchemaPublic.then(rows => console.log('Public Data:', rows));
// queryBuilderSchemaPrivate.then(rows => console.log('Private Data:', rows));

Public Schema Query: select * from `public`.`users` as `u` left join `public`.`pets` as `p` on `u`.`id` = `p`.`idUser` where `u`.`id` = 1
Private Schema Query: select * from `private`.`users` as `u` left join `private`.`pets` as `p` on `u`.`id` = `p`.`idUser` where `u`.`id` = 1