[解決済み] Knex.jsを使ったselect文のサブクエリ

質問

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) から UserCount と AppCount を集計した teams テーブルとなるはずです。

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" と "AppCount" 列が得られますが、常に 0 です。おそらく、サブクエリで '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] 前 knex.ref でKnexに追加されました。 2018年5月 を使用する必要がありました。 knex.raw を、このように。

var teamsIdColumnIdentifier = knex.raw('??', ['teams.ID']);