可以在QueryFile的子句中添加动态吗?

发布于 2025-01-23 04:29:18 字数 1004 浏览 0 评论 0原文

我有一个复杂的查询存储在SQL文件中,我想在各种路线上重复使用它,但根据路由更改Where子句。这将不是在多个文件中具有大型复杂查询,而唯一的区别是where语句。

使用QueryFile时是否可以动态添加一个位置?下面简化的示例:

SELECT "id", "userId", "locationId", "title", "body",
  (
    SELECT row_to_json(sqUser)
    FROM (
      SELECT "id", "firstname", "lastname"
      FROM "users"
          WHERE "users"."id" = "todos"."userId"
      ) sqUser
  ) as "user"
FROM "todos"
const queryIndex = new pgp.QueryFile('sql/todos/index.pgsql', queryOptions);
// 1. Use as is to get a list of all todos
// 2. OR Append WHERE "locationId" = $1 to get list filtered by location
// 3. OR Append WHERE "id" = $1 to get a specific item
// without having three separate SQL files?

似乎(也许?)您可以在查询文件中添加以下内容,但这仍然觉得限制(仍然需要两个文件=喜欢代码>,它仍然只限制在一个条件下)。做一些1 = 1的事情以使所有记录返回,也很奇怪。

WHERE $1 = $2

我有兴趣听到人们对此的想法,或者是否有更好的方法。

I have a complex query stored in an SQL file and I would like to reuse it for various routes but change up the WHERE clause depending on the route. This would be instead of having a large complex query in multiple files with the only difference being the WHERE statement.

Is it possible to dynamically add a WHERE when using QueryFile? Simplified example below:

SELECT "id", "userId", "locationId", "title", "body",
  (
    SELECT row_to_json(sqUser)
    FROM (
      SELECT "id", "firstname", "lastname"
      FROM "users"
          WHERE "users"."id" = "todos"."userId"
      ) sqUser
  ) as "user"
FROM "todos"
const queryIndex = new pgp.QueryFile('sql/todos/index.pgsql', queryOptions);
// 1. Use as is to get a list of all todos
// 2. OR Append WHERE "locationId" = $1 to get list filtered by location
// 3. OR Append WHERE "id" = $1 to get a specific item
// without having three separate SQL files?

It seems like (maybe?) you could get away with adding the below in the query file but that still feels limiting (would still need two files for = and LIKE and it still limits to only one WHERE condition). It also also feels weird to do something like WHERE 1 = 1 to get all records to return.

WHERE $1 = $2

I would be interested in hearing peoples' thoughts on this or if there is a better approach.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

还在原地等你 2025-01-30 04:29:18

您可以将动态条件注入原始文本

SELECT "id", "userId", "locationId", "title", "body",
  (
    SELECT row_to_json(sqUser)
    FROM (
      SELECT "id", "firstname", "lastname"
      FROM "users"
          ${condition:raw}
      ) sqUser
  ) as "user"
FROM "todos"

基于条件:

// Generate a condition, based on the business logic:
const condition = pgp.as.format('WHERE col_1 = $1 AND col_2 = $2', [111, 222]);

执行查询文件:

await db.any(myQueryFile, {condition});

Advanced

的预先格式参数是针对您要在代码中生成的简单动态条件时的情况。但是有时您可能需要交替使用复杂的静态条件。在这种情况下,您可以将主查询文件参考从从设备查询文件(嵌套查询文件在框中支持嵌套查询文件)。在这种情况下,您甚至不需要使用:raw过滤器,因为查询文件默认为RAW文本:

Master查询:

SELECT * FROM table ${condition}

使用复杂条件(应用程序启动时)加载从属查询文件:

const conditionQueryFile1 = new QueryFile(...);
const conditionQueryFile2 = new QueryFile(...);

根据业务逻辑选择右手查询:

const condition = conditionQueryFile1; // some business logic here;

以参数执行主查询:

await db.any(myQueryFile, {condition});

You can inject dynamic condition into a query-file as Raw Text:

SELECT "id", "userId", "locationId", "title", "body",
  (
    SELECT row_to_json(sqUser)
    FROM (
      SELECT "id", "firstname", "lastname"
      FROM "users"
          ${condition:raw}
      ) sqUser
  ) as "user"
FROM "todos"

Pre-formatted parameters, based on the condition:

// Generate a condition, based on the business logic:
const condition = pgp.as.format('WHERE col_1 = $1 AND col_2 = $2', [111, 222]);

Executing your query-file:

await db.any(myQueryFile, {condition});

Advanced

Above is for the scenario when your have a simple dynamic condition that you want to generate in the code. But sometimes you may have complex static conditions that you want to alternate. In this case, you can have your master query file refer to the condition from a slave query file (nested query files are supported right out of the box). And in this case you do not even need to use :raw filter, because query files are injected as raw text by default:

Master query:

SELECT * FROM table ${condition}

Load your slave query files with complex conditions (when the app starts):

const conditionQueryFile1 = new QueryFile(...);
const conditionQueryFile2 = new QueryFile(...);

Selecting the right slave query, based on the business logic:

const condition = conditionQueryFile1; // some business logic here;

Executing master query with a slave as parameter:

await db.any(myQueryFile, {condition});
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文