How to return the number of children for tree nodes in Sequelize?

I have a hierarchical tree data structure defined like this:

module.exports = function(sequelize, DataTypes) {
    var Category = sequelize.define('Category', {
            id: { type: DataTypes.STRING, primaryKey: true },
            parent: DataTypes.STRING,
            text: DataTypes.STRING
        }
    );
    Category.belongsTo(Category, { foreignKey: 'parent' });
    return Category;
};

I have a service returning the list of children for a given node like this:

exports.categoryChildren = function(req, res) {
    var id = req.params.id;
    db.Category.findAll({ where: { parent: id }}).success(function(categories){
        return res.jsonp(categories);
    }).error(function(err){
        return res.render('error', { error: err, status: 500 });
    });
};

Is there a way to make Sequelize to return the number of grandchildren for every child of the given node (i.e. the grandchildren of the given node)?

The SQL query I'd use for that looks like this:

SELECT *, (select count(*) from Categories chld where chld.parent = cat.id) 
FROM `Categories` cat 
WHERE cat.`parent`='my_node_id';

However, I can't find a way to force Sequelize to generate a query like that.

I ran into this same issue. So far the only thing I could figure out to do was using raw queries.

There are just some things ORMs can't do, I think this might be one of those things. You can just use query()

sequelize.query('select * from all_counts').success(function(counts) {...})

I ended up using two nested queries as shown below.

Pros: I'm staying within the ORM paradigm.

Cons: the obvious performance hit.

exports.categoryChildren = function(req, res) {
    var id = req.params.id;

    db.Category.findAll({ where: { parent: id }}).success(function(categories) {
        var categoryIds = [];
        _.each(categories, function(element, index, list) {
            categoryIds[element.id] = element;
        });
        db.Category.findAll({attributes: [['Categories.parent', 'parent'], [Sequelize.fn('count', 'Categories.id'), 'cnt']], where: { parent: { in: _.keys(categoryIds) }}, group: ['Categories.parent']}).success(function(counts) {
            _.each(counts, function(element, index, list) {
                categoryIds[element.dataValues.parent].dataValues.cnt = element.dataValues.cnt;
            });
            return res.jsonp(categories);
        }).error(function(err){
            return res.render('error', {
                error: err,
                status: 500
            });
        });
    }).error(function(err){
        return res.render('error', {
            error: err,
            status: 500
        });
    });
};