Support count

[newlix]

It would be nice to have count support. ex.

dirac.dals.users.count({ id: { $gt: 5 } }, function(error, count){
  /* ... */
});

How to use mo-sql to represent "select Count(*) from users"?

[jrf0110]

In mosql:

{
  type: 'select'
, table: 'users'
, columns: [{ expression: 'count(*)' }]
, where: { id: { $gt: 5 } }
}

[jrf0110]

Count would be a nice function. Here's a plugin to support it:

/* Before dirac.init(...) */
dirac.DAL = dirac.DAL.extend({
  count: function( where, callback ){
    if ( typeof where === 'function' ){
      callback = where;
      where = {};
    }

    var options = {
      columns: [{ expression: 'count(*)', alias: 'count' }]
    };

    return this.find( where, options, function( error, result ){
      if ( error ) return callback( error );

      return callback( null, +result[0].count );
    });
  }
});

Then you can do stuff like:

dirac.dals.users.count( console.log.bind( console ) );
dirac.dals.users.count( { name: { $ilike: 'bob' } }, console.log.bind( console ) );

[newlix]

Thank you very much. The solution is so neat. I really love your libraries. They deserve more promotion.

Btw, sometime I need to count on a joined table, so I think the options parameter is also necessary.

[jrf0110]

@newlix thanks! That means a lot :)

I think if you're doing more complex counts, then you're better off just using the expression helper in the columns array

[jrf0110]

Also, just a heads up, there will be some big changes come v1.0 https://github.com/jrf0110/dirac/issues/54 put your thoughts there

[newlix]

How can I do a distinct count ?

[jrf0110]

You mean something equivalent to:

select
  distinct on (organization)
  count(*)
from users
group by organization;

[jrf0110]

If so, then we can go off our plugin we added up there (assuming we added the options parameter)

dirac.dals.users.count( {}, {
  distinct: ['organization']
, groupBy: ['organization']
})

Where the second argument will get mixed into the options query

[newlix]

But this will return something like [{count:1}, {count:10}, ...] I expected we can have

SELECT COUNT(DISTINCT column_name) FROM table_name; 

[jrf0110]

you could do a couple of things. If you're not worried about having a semantic column list, then the expression helper will do what you need:

{
  columns: [{ expression: 'count( distinct column_name )' }, ...]
}

If you're just wanting something more semantic, you can register a new query type:

dirac.db.mosql.registerQueryType(
  'count-distinct'
, 'count( distinct {columns} )
);

And do something like:

{
  columns: [{ type: 'count-distinct', columns: ['column_name'] }, { ... }]
}

Even that's a little crappy.

What would be nice is if we could do this:

{
  columns: [{ distinct: 'column_name' }, { distinct: 'column_name2' }, ...]
}

Hrmm..