英文:
How to add "FETCH FIRST n ROWS ONLY" using Kysely DB query builder?
问题
我喜欢在基于TypeScript的项目中使用的DB工具是Prisma。但是,其中一个项目正在使用Kysely类型安全的DB查询构建器。
我正试图准备一个使用Kysely查询构建器处理键集分页的查询,但我无法弄清如何将 FETCH FIRST n ROWS ONLY
添加到查询中,如果可能的话。
Kysely文档链接: https://kysely.dev/docs/category/select
有任何想法或建议吗?
import { Kysely, sql } from "kysely";
const DB = new Kysely<Database>({...});
const query = DB
.selectFrom("cl")
.selectAll()
.where(sql`(created_at, id)`, '>', `('${keyFromCreatedAt}', '${keyFromId}')`)
// 在这里插入 FETCH FIRST n ROWS ONLY
;
const res = query.execute();
希望这对你有所帮助。
英文:
My favorite DB toolkit for use with TypeScrypt-based projects is Prisma. However, one of the projects is using Kysely type-safe DB query builder.
I am trying to prepare a query that would handle keyset pagination using Kysely query builder, but I can't figure out how to add FETCH FIRST n ROWS ONLY
into the query if that is even possible.
Kysely docs: https://kysely.dev/docs/category/select
Any ideas or suggestions?
import { Kysely, sql } from "kysely";
const DB = new Kysely<Database>({...});
const query = DB
.selectFrom("cl")
.selectAll()
.where(sql`(created_at, id)`, '>', `('${keyFromCreatedAt}', '${keyFromId}')`)
// ¿¿¿ Something to insert FETCH FIRST n ROWS ONLY? ???
;
const res = query.execute();
答案1
得分: 2
Here are the translated code parts:
首先,您可以使用LIMIT
子句(SelectQueryBuilder.limit(n)
)作为FETCH FIRST n ROWS ONLY
的替代方法,具体取决于您使用的方言。如果必须使用FETCH FIRST n ROWS ONLY
,您应该实现自己的QueryCompiler
。
这不会花费很多时间。一切都已经在DefaultQueryCompiler中实现了。您只需扩展它并覆盖一个方法。例如,PostgresQueryCompiler覆盖了DefaultQueryCompiler中的单个方法。
export class PostgresQueryCompiler extends DefaultQueryCompiler {
protected override sanitizeIdentifier(identifier: string): string {
return identifier.replace(ID_WRAP_REGEX, '""')
}
}
定义您的自定义QueryCompiler并覆盖visitLimit
方法以更改limit()
的行为。
class MyQueryCompiler extends PostgresQueryCompiler {
protected visitLimit(node: LimitNode) {
this.append("fetch next ")
this.visitNode(node.limit);
this.append(" rows only")
}
}
然后扩展Dialect
类并覆盖createQueryCompiler()
方法以使用QueryCompiler
。
class MyDialect extends PostgresDialect {
createQueryCompiler(): QueryCompiler {
return new MyQueryCompiler();
}
}
将其传递给Kysely
构造函数以使用它。
const db = new Kysely<DB>({
dialect: new MyDialect(...),
})
PostgresQueryCompiler
和PostgresDialect
只是示例。您可以覆盖任何您想要的方言。
完整示例:
import {
Kysely, LimitNode,
PostgresDialect, PostgresQueryCompiler,
QueryCompiler
} from "kysely";
class MyQueryCompiler extends PostgresQueryCompiler {
protected visitLimit(node: LimitNode) {
this.append("fetch next ")
this.visitNode(node.limit);
this.append(" rows only")
}
}
class MyDialect extends PostgresDialect {
createQueryCompiler(): QueryCompiler {
return new MyQueryCompiler();
}
}
const db = new Kysely<any>({
dialect: new MyDialect({} as any),
})
const {sql} = db.selectFrom("table").select(["a", "b"]).limit(10).offset(20).compile()
console.log(sql) // select "a", "b" from "table" fetch next $1 rows only offset $2
英文:
First of all, you can use LIMIT
clause (SelectQueryBuilder.limit(n)
) as an alternative for FETCH FIRST n ROWS ONLY
depending on the dialect you are using. If you have to use FETCH FIRST n ROWS ONLY
, you should implement you own QueryCompiler
.
It doesn't take a lot of time. Everything is already implemented in DefaultQueryCompiler. All you should do is extend it and override a single method. For example, PostgresQueryCompiler overrides single method from DefaultQueryCompiler.
export class PostgresQueryCompiler extends DefaultQueryCompiler {
protected override sanitizeIdentifier(identifier: string): string {
return identifier.replace(ID_WRAP_REGEX, '""')
}
}
Defined your custom QueryCompiler and override visitLimit
method to change the behavior of limit()
.
class MyQueryCompiler extends PostgresQueryCompiler {
protected visitLimit(node: LimitNode) {
this.append("fetch next ")
this.visitNode(node.limit);
this.append(" rows only")
}
}
Then extends a Dialect
class and override createQueryCompiler()
method to use the QueryCompiler
.
class MyDialect extends PostgresDialect {
createQueryCompiler(): QueryCompiler {
return new MyQueryCompiler();
}
}
Pass it to Kysely
constructor to use it.
const db = new Kysely<DB>({
dialect:new MyDialect(...),
})
PostgresQueryCompiler
and PostgresDialect
are just examples. You can override any dialect you want.
Full example:
import {
Kysely, LimitNode,
PostgresDialect, PostgresQueryCompiler,
QueryCompiler
} from "kysely";
class MyQueryCompiler extends PostgresQueryCompiler {
protected visitLimit(node: LimitNode) {
this.append("fetch next ")
this.visitNode(node.limit);
this.append(" rows only")
}
}
class MyDialect extends PostgresDialect {
createQueryCompiler(): QueryCompiler {
return new MyQueryCompiler();
}
}
const db = new Kysely<any>({
dialect: new MyDialect({} as any),
})
const {sql} = db.selectFrom("table").select(["a", "b"]).limit(10).offset(20).compile()
console.log(sql) // select "a", "b" from "table" fetch next $1 rows only offset $2
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论