title: Subquery of select statement with Knex.js
body: <p>I am trying to create the following query with subqueries using Knex:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>The result should be the teams table with a count aggregation of UserCount and AppCount from different tables (team_users, team_access)</p>
  
  <pre><code>id | Name      | OwnerUserID   | UserCount | AppCount
  -----------------------------------------------------
  134| Team A    | 1538          | 7         | 6
  135| Team B    | 1538          | 4         | 2
  136| Team C    | 1538          | 12        | 1
  </code></pre>
  
  <p>What I figured to be an equivalent knex implementation was:</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>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.</p>
  
  <p>I managed to solve it using the Knex.raw function: </p>
  
  <pre><code>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);
  </code></pre>
  
  <p>but I am curious to know how to achieve this with the subqueries objects. </p>