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 can use subqueries in SELECT, INSERT, UPDATE, and DELETE statements wherever expressions are allowed. For instance, you can use a subquery as one of the column expressions in a SELECT list or as a table expression in the FROM clause. A DML statement that includes a subquery is referred to as the outer query.
A subquery (the inner SELECT statement, where one SELECT statement is nested within another) can return zero or more rows or expressions. Each subquery must be delimited by parentheses, and must contain a Projection clause and a FROM clause.
Knex. raw insert does not return a number of rows inserted to the table. It returns empty array [] But knex.
Sequelize is an ORM that includes some query builder stuff; Knex is just a query builder, not an ORM.
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']);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With