{"action":"opened","issue":{"url":"https://api.github.com/repos/overlookmotel/sequelize-hierarchy/issues/45","labels_url":"https://api.github.com/repos/overlookmotel/sequelize-hierarchy/issues/45/labels{/name}","comments_url":"https://api.github.com/repos/overlookmotel/sequelize-hierarchy/issues/45/comments","events_url":"https://api.github.com/repos/overlookmotel/sequelize-hierarchy/issues/45/events","html_url":"https://github.com/overlookmotel/sequelize-hierarchy/issues/45","id":123781643,"number":45,"title":"Counting related objects of descendants","user":{"login":"bonnzer","id":1415392,"avatar_url":"https://avatars.githubusercontent.com/u/1415392?v=3","gravatar_id":"","url":"https://api.github.com/users/bonnzer","html_url":"https://github.com/bonnzer","followers_url":"https://api.github.com/users/bonnzer/followers","following_url":"https://api.github.com/users/bonnzer/following{/other_user}","gists_url":"https://api.github.com/users/bonnzer/gists{/gist_id}","starred_url":"https://api.github.com/users/bonnzer/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/bonnzer/subscriptions","organizations_url":"https://api.github.com/users/bonnzer/orgs","repos_url":"https://api.github.com/users/bonnzer/repos","events_url":"https://api.github.com/users/bonnzer/events{/privacy}","received_events_url":"https://api.github.com/users/bonnzer/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":0,"created_at":"2015-12-24T09:37:36Z","updated_at":"2015-12-24T09:37:36Z","closed_at":null,"body":"I have two related models: Category and Product (Category is hierarchical).\r\n\r\n```\r\nsequelize.define('Category', {\r\n    name:           {\r\n      type: DataTypes.STRING,\r\n      allowNull: false\r\n    }\r\n  }, {\r\n    hierarchy: true,\r\n    classMethods: {\r\n      associate: function(models) {\r\n        Category.hasMany(models.Product, { as: 'products' });\r\n      }\r\n    }\r\n  })\r\n```\r\nand\r\n```\r\nsequelize.define('Product', {\r\n  name:           {\r\n    type: DataTypes.STRING,\r\n    allowNull: false\r\n  }\r\n}, {\r\n  classMethods: {\r\n    associate: function(models) {\r\n      Product.belongsTo(models.Category, { as: 'category' });\r\n    }\r\n  }\r\n})\r\n```\r\n\r\nWhen I try to fetch all categories with descendants and Products count on each descendant - I get just one parent Category (parentId: null) and one descendant on it with wrong \"count\" value. There is my code:\r\n\r\n```\r\nmodels\r\n  .Category\r\n  .findAll({\r\n    where  : {\r\n      parentId: null\r\n    },\r\n    include: [{ \r\n      model: models.Category, \r\n      as:    'descendents', \r\n      hierarchy: true,\r\n      attributes:\r\n        Object.keys(models.Category.attributes).concat([\r\n          [Sequelize.fn('COUNT', Sequelize.col('descendents.products.id')), \"count\"]\r\n        ]),\r\n      include:[{\r\n        model: models.Product,\r\n        as: \"products\",\r\n        where: { \r\n          StatusId: 1\r\n        },\r\n        required: false\r\n      }]\r\n    }],\r\n    order: [\r\n      ['sort', 'ASC'],\r\n      [ { model: models.Category, as: 'descendents' }, 'sort', 'ASC' ]\r\n    ]\r\n  })\r\n```\r\nP.S. removing \"where\" and \"required\" statements from Product include don't affect anything"}}