Subquery of select statement with Knex.js

Question

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.

Answer 1

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']);