I am trying to create the following query with subqueries using 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;
The result should be the teams table with a count aggregation of UserCount and AppCount from different tables (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
What I figured to be an equivalent knex implementation was:
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);
Running that, I do get the "UserCount" and "AppCount" columns in the returned object but always as zero, probably because it doesn't identify the 'teams.ID' in the subquery.
I managed to solve it using the Knex.raw function:
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);
but I am curious to know how to achieve this with the subqueries objects.
You are trying to pass teams.ID
string as a value. To be able to do .where('columnName', 'otherColumnName')
one has to use knex.ref
to pass otherColumnName
as an identifier.
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] Before knex.ref
was added to Knex in May 2018, you had to use knex.raw
, like this;
var teamsIdColumnIdentifier = knex.raw('??', ['teams.ID']);