Contagem por grupo no BookshelfJS para SQL

BookshelfJS é um javascript ORM para Node.js, construído no construtor de consulta Knex SQL. Se você tiver uma consulta complicada, use KnexJS , a menos que queira ler o código-fonte

Vamos para o meu caso de estudo. Eu tenho a exigência de contar o total de jogadores por jogo dos registros do histórico do jogo e uso o BookshelfJS para o SQL ORM.

// == GameHistory Model
//
// Table name:  game_histories
// Model path:  app_root/app/models/game_history.js
// Columns:
//  id            :integer          not null, primary key
//  game_id       :integer
//  user_id       :integer
//  credit        :integer
//  point         :integer
//  created_at    :datetime
//  updated_at    :datetime
//

var Game = require('./game')();
var Player = require('./player')();
var _ = require('underscore');

var InstanceMethods = {
 tableName: 'game_histories',
 game: function(){ 
  return this.belongsTo(Game, 'game_id');
 },
 player: function(){
  return this.belongsTo(Player, 'user_id');
 }
 // ... more properties
}

var ClassMethods = {
 mostPlayed: function(_max){
  if(!Number(_max)) { _max = 10; }
   var qDB = this.collection().query();
   var qRaw = 'COUNT(game_histories.*) AS total_players, game_id, games.*';    
   return qDB.join('games', 'games.id', '=', 'game_histories.game_id').
    select(qDB.knex.raw(qRaw)).
    groupBy('game_histories.game_id', 'games.id').
    orderBy('total_players', 'desc').
    limit(_max);
 },
  // ... more static functions
}

module.exports = function(){
  return CoreDb.Model.extend( InstanceMethods, ClassMethods );
};

// CoreDb is global variable, snipped code is like this:
//   global.CoreDb = Bookshelf.initialize({
//      client: 'pg',
//      connection: config.db_connection
//   });

O uso:

// Get top 10 Most Played Games in pagination.
var Game = require('./app/models/game')();

app.get('/games/top/:max', function(req, res, next){

  Game.mostPlayed(req.params.max).done(function(games){
    res.locals.games = games;
    res.render('games/top');
  })

})

Os resultados:

[
{total_players: 400, name: 'rolling birds'}
{total_players: 1101, name: 'lucky ninja'},
{total_players: 199, name: 'motor theft'},
...
]