Count by group in BookshelfJS for SQL

BookshelfJS is a javascript ORM for Node.js, built on the Knex SQL query builder. If you have complicated query, please use KnexJS, unless you want read the source code

Let's go to my study case. I got requirement to count total players per game from game history's records and I use BookshelfJS for the 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
//   });

The usage:

// 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');
  })

})

The results:

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