PostgreSQL子查询关联语法错误：如何用Knex构建正确查询？

我有一段可正常运行的PostgreSQL查询语句：

select * from "A" left join (
    select distinct on ("provider_id") * from "B" order by "provider_id" asc, "date" desc
) B using ("provider_id") where "A"."date" >= '2022-02-17T08:31:46.781Z' order by "A"."date" desc limit 25;

尝试用Knex的.leftJoin方法构建查询：

query
  .orderBy("date", orderBy.sort)
  .limit(Math.min(limit, 1000))
  .offset(Math.min(offset, 100000))
  .leftJoin(
    this.db('B')
     .distinctOn('provider_id')
     .select('*')
     .orderBy([
       {
         column: 'provider_id',
       },
       {
         column: 'date',
         order: 'desc',
       },
    ]),
    'A.provider_id',
    'B.provider_id'
  )

也试过结合.raw的方式：

query
  .orderBy("date", orderBy.sort)
  .limit(Math.min(limit, 1000))
  .offset(Math.min(offset, 100000))
  .leftJoin(
    this.db.raw('select distinct on ("provider_id") * from "B" order by "provider_id", "date" desc'),
    'A.provider_id',
    'B.provider_id'
  )

但生成的SQL语句缺少子查询的括号和别名，导致语法错误：

select * from "A" left join select distinct on ("provider_id") * from "B" order by "provider_id", "date" desc on "A"."provider_id" = "B"."provider_id" where "date" >= '2022-02-17T09:09:08.148Z' order by "date" desc limit 25

执行时抛出错误：

ERROR:  syntax error at or near "select"
LINE 62: select * from "A" left join select distinct on ("provid...
                                          ^
SQL state: 42601
Character: 3951

解决方法 #

方法1：给Knex子查询添加.as()别名

Knex中，子查询需要用.as()指定别名，框架会自动给子查询加上括号并包裹别名。修改后的代码如下：

query
  .where('A.date', '>=', '2022-02-17T08:31:46.781Z')
  .orderBy('A.date', 'desc')
  .limit(25)
  .leftJoin(
    this.db('B')
     .distinctOn('provider_id')
     .select('*')
     .orderBy([
       { column: 'provider_id' },
       { column: 'date', order: 'desc' }
     ])
     .as('B'), // 关键：添加as('B')指定别名
    'A.provider_id',
    'B.provider_id'
  );

方法2：在raw语句中手动添加括号和别名

如果用.raw的方式，需要自己给子查询加上括号并指定别名，Knex不会自动处理这部分：

query
  .where('A.date', '>=', '2022-02-17T08:31:46.781Z')
  .orderBy('A.date', 'desc')
  .limit(25)
  .leftJoin(
    this.db.raw('(select distinct on ("provider_id") * from "B" order by "provider_id", "date" desc) B'), // 手动加括号和别名B
    'A.provider_id',
    'B.provider_id'
  );

两种方法生成的SQL都会和原始的正确语句一致，解决语法错误问题。

内容的提问来源于stack exchange，提问作者srgbnd