可以在QueryFile的子句中添加动态吗?
我有一个复杂的查询存储在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 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
您可以将动态条件注入原始文本:
基于条件:
执行查询文件:
Advanced
的预先格式参数是针对您要在代码中生成的简单动态条件时的情况。但是有时您可能需要交替使用复杂的静态条件。在这种情况下,您可以将主查询文件参考从从设备查询文件(嵌套查询文件在框中支持嵌套查询文件)。在这种情况下,您甚至不需要使用
:raw
过滤器,因为查询文件默认为RAW文本:Master查询:
使用复杂条件(应用程序启动时)加载从属查询文件:
根据业务逻辑选择右手查询:
以参数执行主查询:
You can inject dynamic condition into a query-file as Raw Text:
Pre-formatted parameters, based on the condition:
Executing your query-file:
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:
Load your slave query files with complex conditions (when the app starts):
Selecting the right slave query, based on the business logic:
Executing master query with a slave as parameter: