我正在尝试使用knex使用子征询来创建以下查询:
SELECT
t.*,
(SELECT COUNT(*) FROM team_users tu WHERE TeamID = t.ID) AS UserCount,
(SELECT COUNT(*) FROM team_access ta WHERE TeamID = t.ID) AS AppCount
FROM teams t WHERE OwnerUserID = _UserID;
结果应该是团队表,并从不同表(team_users,team_access)
id | Name | OwnerUserID | UserCount | AppCount
-----------------------------------------------------
134| Team A | 1538 | 7 | 6
135| Team B | 1538 | 4 | 2
136| Team C | 1538 | 12 | 1
我认为是等效的knex实施是:
var subquery1 = Knex.knex('team_users').count('*').where('TeamID', 'teams.ID').as('UserCount');
var subquery2 = Knex.knex('team_access').count('*').where('TeamID', 'teams.ID').as('AppCount');
Knex.knex.select('*', subquery1, subquery2).from('teams').where("OwnerUserID", ownerId).asCallback(dataSetCallback);
运行它,我确实在返回的对象中获得了" USERCOUNT"one_answers" APPCOUNT"列,但始终为零,可能是因为它在子查询中没有识别'teams.ID'。
我设法使用knex.raw函数解决了它:
Knex.knex('teams')
.select('*', Knex.knex.raw('(SELECT COUNT(*) FROM team_users WHERE TeamID = teams.ID) AS UserCount'), Knex.knex.raw('(SELECT COUNT(*) FROM team_access WHERE TeamID = teams.ID) AS AppCount'))
.where("OwnerUserID", ownerId)
.asCallback(dataSetCallback);
,但我很想知道如何使用子征服对象实现这一目标。
您正在尝试将teams.ID
字符串作为值传递。为了能够做.where('columnName', 'otherColumnName')
,必须使用knex.ref
将otherColumnName
作为标识符。
var teamsIdColumnIdentifier = knex.ref('teams.ID'); // <-- [1]
var subquery1 = Knex.knex('team_users').count('*')
.where('TeamID', teamsIdColumnIdentifier).as('UserCount');
var subquery2 = Knex.knex('team_access').count('*')
.where('TeamID', teamsIdColumnIdentifier).as('AppCount');
Knex.knex.select('*', subquery1, subquery2).from('teams')
.where("OwnerUserID", ownerId).asCallback(dataSetCallback);
[1]在2018年5月将knex.ref
添加到KNEX之前,您必须使用knex.raw
,例如
var teamsIdColumnIdentifier = knex.raw('??', ['teams.ID']);