时间戳

首页 > 解决方案 > knex.js 中的子查询或 knex.js 中使用原始或任何替代方式的直接 postgresql 查询

node.js - knex.js 中的子查询或 knex.js 中使用原始或任何替代方式的直接 postgresql 查询

问题描述

我目前在 express.js 项目中工作，并使用 knex 进行数据库迁移和查询。我在编写查询以获得我在 postgresql (dbeaver) 中得到的确切结果时遇到了麻烦。下面给出了我需要转换为 knex 查询的查询（来自表）。实际上想要根据列的不同值获取列的计数（应用程序）

select count(job_id) as total_applicants ,

(select count(job_id) from applications as APPL join 
recruiter_positions as POS on POS.position_id=APPL.job_id
where APPL.state='qualified' and POS.creator_id='e5f604be-e773-490b- 
a525-fad3b95fd941') as qlCount,
(select count(job_id) from applications as APPL join 
recruiter_positions as POS on POS.position_id=APPL.job_id
where APPL.state='backup' and POS.creator_id='e5f604be-e773-490b-a525- 
fad3b95fd941') as bckCount,
(select count(job_id) from applications as APPL join 
recruiter_positions as POS on POS.position_id=APPL.job_id
where APPL.state='interview' and POS.creator_id='e5f604be-e773-490b- 
a525-fad3b95fd941') as intrCount,
(select count(job_id) from applications as APPL join 
recruiter_positions as POS on POS.position_id=APPL.job_id
where APPL.state='offer' and POS.creator_id='e5f604be-e773-490b-a525- 
fad3b95fd941') as offrCount,
(select count(job_id) from applications as APPL join 
recruiter_positions as POS on POS.position_id=APPL.job_id
where APPL.state='screening' and POS.creator_id='e5f604be-e773-490b- 
a525-fad3b95fd941') as screenCount,
(select count(job_id) from applications as APPL join 
recruiter_positions as POS on POS.position_id=APPL.job_id
where APPL.state='rejected' and POS.creator_id='e5f604be-e773-490b- 
a525-fad3b95fd941') as rejectedCount

from applications as APPL join recruiter_positions as POS on 
POS.position_id=APPL.job_id
where APPL.state notnull and POS.creator_id='e5f604be-e773-490b-a525- 
fad3b95fd941'

标签： node.jsdatabasepostgresqlknex.js

解决方案

在不为您转换整个事情的情况下，这是子查询在 Knex 中的工作方式。

每个查询都返回一个 Knex 查询构建器的实例，我们将其别名为.as. 所以逐渐建立它们很容易：

const subquery1 = knex("foos").count("*").as("foocount");
const subquery2 = knex("bars").count("*").as("barcount");
knex.select(subquery1, subquery2).then(console.log);

推荐阅读