Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Subquery of select statement with Knex.js

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.

like image 703
Didi Avatar asked Feb 21 '17 12:02

Didi


People also ask

Can you do a subquery in a select statement?

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.

When can subquery be used in select statement?

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.

What does KNEX insert return?

Knex. raw insert does not return a number of rows inserted to the table. It returns empty array [] But knex.

Is KNEX an ORM?

Sequelize is an ORM that includes some query builder stuff; Knex is just a query builder, not an ORM.


1 Answers

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']);
like image 128
Mikael Lepistö Avatar answered Sep 17 '22 01:09

Mikael Lepistö