sequalize include total number of association

.gitignore

/node_modules

package.json

{
  "dependencies": {
    "mysql2": "^1.5.3",
    "sequelize": "^4.37.6"
  },
  "private": true,
  "license": "MIT"
}

query.js

const Sequelize = require('sequelize');
const sequelize = new Sequelize('hoge', 'root', 'password', {
  host: 'localhost',
  dialect: 'mysql',
  operatorsAliases: false,
  pool: {
    max: 1,
    min: 0,
    acquire: 30000,
    idle: 10000
  }
});

const User = sequelize.define('user', {
  name: {
    type: Sequelize.STRING
  },
});

const Post = sequelize.define('post', {
  title: {
    type: Sequelize.STRING
  },
  status: {
    type: Sequelize.STRING,
    values: ['draft', 'published', 'deleted' ],
    defaultValue: 'draft',
  },
});

Post.belongsTo(User, { as: 'author' });
User.hasMany(Post, { foreignKey: 'authorId' });

async function prepareData() {
  await User.sync();
  await Post.sync();
  await Promise.all([
    Post.destroy({ where: {}}),
    User.destroy({ where: {}}),
  ]);

  await createUserAndPosts('Tom', 1);
  await createUserAndPosts('Second User', 0);
  await createUserAndPosts('Writer', 3);
  await createUserAndPosts('Editor', 6);
  await createUserAndPosts('Inactive User', 0);
  await createUserAndPosts('Inactive User2', 10, true);
}

async function createUserAndPosts(name, numPosts, deleteOnly = false) {
  const user = await User.create({
    name,
  });
  const postPromises = [];
  for (let i = 0; i < numPosts; i++) {
    let status = 'draft';
    if (i % 3 === 0) {
      status = 'published';
    } else if (i % 3 === 1) {
      status = 'deleted';
    }
    if (deleteOnly) {
      status = 'deleted';
    }
    const post = Post.create({
      title: `post by ${name} - ${i+1}`,
      authorId: user.get('id'),
      status,
    });
    postPromises.push(post);
 
  }
  await Promise.all(postPromises);
}

main().then(() => {
  process.exit(0);
}).catch(err => {
  console.error(err);
  process.exit(1);
});

async function main() {
  await prepareData();
  const [count, rows] = await Promise.all([
    getPostCount(),
    // getUsersWithPostCount(),
    getUsersWithPosts(),
  ]);
  const data = { count, rows };
  console.log(JSON.stringify(data, '  ', 2));
}

async function getUsersWithPosts() {
  const rows = await User.findAll({
    limit: 10,
    offset: 0,
    attributes: [
      'id',
      'name',
    ],
    include: [
      {
        model: Post,
        as: 'posts',
        required: true,
        where: {
          status: {
            [Sequelize.Op.or]: ['draft', 'published']
          },
        },
      },
    ],
  });
  return rows.map(row => {
    return row.get({ plain: true });
  })
}

async function getUsersWithPostCount() {
  const rows = await User.findAll({
    limit: 10,
    offset: 0,
    group: ['user.id'],
    attributes: [
      'id',
      'name',
      [sequelize.fn('count', sequelize.col('posts.id')), 'postCount'],
    ],
    include: [
      {
        model: Post,
        attributes: [],
        duplicating: false,
        where: {
          status: {
            [Sequelize.Op.or]: ['draft', 'published']
          }
        }
      }
    ],
    raw: true
  });

  return rows;
}

async function getPostCount() {
  const count = await User.count({
    distinct: true,
    include: [
      {
        model: Post,
        attributes: [],
        duplicating: false,
        where: {
          status: {
            [Sequelize.Op.or]: ['draft', 'published']
          }
        }
      }
    ],
    raw: true
  });

  return count;
}

yarn.lock

# THIS IS AN AUTOGENERATED FILE. DO NOT EDIT THIS FILE DIRECTLY.
# yarn lockfile v1


"@types/geojson@^1.0.0":
  version "1.0.6"
  resolved "https://registry.yarnpkg.com/@types/geojson/-/geojson-1.0.6.tgz#3e02972728c69248c2af08d60a48cbb8680fffdf"

"@types/node@*":
  version "9.6.5"
  resolved "https://registry.yarnpkg.com/@types/node/-/node-9.6.5.tgz#ee700810fdf49ac1c399fc5980b7559b3e5a381d"

ansicolors@~0.2.1:
  version "0.2.1"
  resolved "https://registry.yarnpkg.com/ansicolors/-/ansicolors-0.2.1.tgz#be089599097b74a5c9c4a84a0cdbcdb62bd87aef"

bluebird@^3.4.6, bluebird@^3.5.0:
  version "3.5.1"
  resolved "https://registry.yarnpkg.com/bluebird/-/bluebird-3.5.1.tgz#d9551f9de98f1fcda1e683d17ee91a0602ee2eb9"

cardinal@1.0.0:
  version "1.0.0"
  resolved "https://registry.yarnpkg.com/cardinal/-/cardinal-1.0.0.tgz#50e21c1b0aa37729f9377def196b5a9cec932ee9"
  dependencies:
    ansicolors "~0.2.1"
    redeyed "~1.0.0"

cls-bluebird@^2.1.0:
  version "2.1.0"
  resolved "https://registry.yarnpkg.com/cls-bluebird/-/cls-bluebird-2.1.0.tgz#37ef1e080a8ffb55c2f4164f536f1919e7968aee"
  dependencies:
    is-bluebird "^1.0.2"
    shimmer "^1.1.0"

core-util-is@~1.0.0:
  version "1.0.2"
  resolved "https://registry.yarnpkg.com/core-util-is/-/core-util-is-1.0.2.tgz#b5fd54220aa2bc5ab57aab7140c940754503c1a7"

debug@^2.6.9:
  version "2.6.9"
  resolved "https://registry.yarnpkg.com/debug/-/debug-2.6.9.tgz#5d128515df134ff327e90a4c93f4e077a536341f"
  dependencies:
    ms "2.0.0"

debug@^3.1.0:
  version "3.1.0"
  resolved "https://registry.yarnpkg.com/debug/-/debug-3.1.0.tgz#5bb5a0672628b64149566ba16819e61518c67261"
  dependencies:
    ms "2.0.0"

denque@1.2.3:
  version "1.2.3"
  resolved "https://registry.yarnpkg.com/denque/-/denque-1.2.3.tgz#98c50c8dd8cdfae318cc5859cc8ee3da0f9b0cc2"

depd@^1.1.0:
  version "1.1.2"
  resolved "https://registry.yarnpkg.com/depd/-/depd-1.1.2.tgz#9bcd52e14c097763e749b274c4346ed2e560b5a9"

dottie@^2.0.0:
  version "2.0.0"
  resolved "https://registry.yarnpkg.com/dottie/-/dottie-2.0.0.tgz#da191981c8b8d713ca0115d5898cf397c2f0ddd0"

esprima@~3.0.0:
  version "3.0.0"
  resolved "https://registry.yarnpkg.com/esprima/-/esprima-3.0.0.tgz#53cf247acda77313e551c3aa2e73342d3fb4f7d9"

generate-function@^2.0.0:
  version "2.0.0"
  resolved "https://registry.yarnpkg.com/generate-function/-/generate-function-2.0.0.tgz#6858fe7c0969b7d4e9093337647ac79f60dfbe74"

generic-pool@^3.4.0:
  version "3.4.2"
  resolved "https://registry.yarnpkg.com/generic-pool/-/generic-pool-3.4.2.tgz#92ff7196520d670839a67308092a12aadf2f6a59"

iconv-lite@^0.4.18:
  version "0.4.21"
  resolved "https://registry.yarnpkg.com/iconv-lite/-/iconv-lite-0.4.21.tgz#c47f8733d02171189ebc4a400f3218d348094798"
  dependencies:
    safer-buffer "^2.1.0"

inflection@1.12.0:
  version "1.12.0"
  resolved "https://registry.yarnpkg.com/inflection/-/inflection-1.12.0.tgz#a200935656d6f5f6bc4dc7502e1aecb703228416"

inherits@~2.0.3:
  version "2.0.3"
  resolved "https://registry.yarnpkg.com/inherits/-/inherits-2.0.3.tgz#633c2c83e3da42a502f52466022480f4208261de"

is-bluebird@^1.0.2:
  version "1.0.2"
  resolved "https://registry.yarnpkg.com/is-bluebird/-/is-bluebird-1.0.2.tgz#096439060f4aa411abee19143a84d6a55346d6e2"

isarray@~1.0.0:
  version "1.0.0"
  resolved "https://registry.yarnpkg.com/isarray/-/isarray-1.0.0.tgz#bb935d48582cba168c06834957a54a3e07124f11"

lodash@^4.17.1:
  version "4.17.5"
  resolved "https://registry.yarnpkg.com/lodash/-/lodash-4.17.5.tgz#99a92d65c0272debe8c96b6057bc8fbfa3bed511"

long@^4.0.0:
  version "4.0.0"
  resolved "https://registry.yarnpkg.com/long/-/long-4.0.0.tgz#9a7b71cfb7d361a194ea555241c92f7468d5bf28"

lru-cache@2.5.0:
  version "2.5.0"
  resolved "https://registry.yarnpkg.com/lru-cache/-/lru-cache-2.5.0.tgz#d82388ae9c960becbea0c73bb9eb79b6c6ce9aeb"

lru-cache@4.1.1:
  version "4.1.1"
  resolved "https://registry.yarnpkg.com/lru-cache/-/lru-cache-4.1.1.tgz#622e32e82488b49279114a4f9ecf45e7cd6bba55"
  dependencies:
    pseudomap "^1.0.2"
    yallist "^2.1.2"

moment-timezone@^0.5.14:
  version "0.5.16"
  resolved "https://registry.yarnpkg.com/moment-timezone/-/moment-timezone-0.5.16.tgz#661717d5f55b4d2c2e002262d726c83785192a5a"
  dependencies:
    moment ">= 2.9.0"

"moment@>= 2.9.0", moment@^2.20.0:
  version "2.22.1"
  resolved "https://registry.yarnpkg.com/moment/-/moment-2.22.1.tgz#529a2e9bf973f259c9643d237fda84de3a26e8ad"

ms@2.0.0:
  version "2.0.0"
  resolved "https://registry.yarnpkg.com/ms/-/ms-2.0.0.tgz#5608aeadfc00be6c2901df5f9861788de0d597c8"

mysql2@^1.5.3:
  version "1.5.3"
  resolved "https://registry.yarnpkg.com/mysql2/-/mysql2-1.5.3.tgz#d905a1a06da0469828287608ce68647b8130748f"
  dependencies:
    cardinal "1.0.0"
    denque "1.2.3"
    generate-function "^2.0.0"
    iconv-lite "^0.4.18"
    long "^4.0.0"
    lru-cache "4.1.1"
    named-placeholders "1.1.1"
    object-assign "^4.1.1"
    readable-stream "2.3.5"
    safe-buffer "^5.0.1"
    seq-queue "0.0.5"
    sqlstring "2.3.1"

named-placeholders@1.1.1:
  version "1.1.1"
  resolved "https://registry.yarnpkg.com/named-placeholders/-/named-placeholders-1.1.1.tgz#3b7a0d26203dd74b3a9df4c9cfb827b2fb907e64"
  dependencies:
    lru-cache "2.5.0"

object-assign@^4.1.1:
  version "4.1.1"
  resolved "https://registry.yarnpkg.com/object-assign/-/object-assign-4.1.1.tgz#2109adc7965887cfc05cbbd442cac8bfbb360863"

process-nextick-args@~2.0.0:
  version "2.0.0"
  resolved "https://registry.yarnpkg.com/process-nextick-args/-/process-nextick-args-2.0.0.tgz#a37d732f4271b4ab1ad070d35508e8290788ffaa"

pseudomap@^1.0.2:
  version "1.0.2"
  resolved "https://registry.yarnpkg.com/pseudomap/-/pseudomap-1.0.2.tgz#f052a28da70e618917ef0a8ac34c1ae5a68286b3"

readable-stream@2.3.5:
  version "2.3.5"
  resolved "https://registry.yarnpkg.com/readable-stream/-/readable-stream-2.3.5.tgz#b4f85003a938cbb6ecbce2a124fb1012bd1a838d"
  dependencies:
    core-util-is "~1.0.0"
    inherits "~2.0.3"
    isarray "~1.0.0"
    process-nextick-args "~2.0.0"
    safe-buffer "~5.1.1"
    string_decoder "~1.0.3"
    util-deprecate "~1.0.1"

redeyed@~1.0.0:
  version "1.0.1"
  resolved "https://registry.yarnpkg.com/redeyed/-/redeyed-1.0.1.tgz#e96c193b40c0816b00aec842698e61185e55498a"
  dependencies:
    esprima "~3.0.0"

retry-as-promised@^2.3.2:
  version "2.3.2"
  resolved "https://registry.yarnpkg.com/retry-as-promised/-/retry-as-promised-2.3.2.tgz#cd974ee4fd9b5fe03cbf31871ee48221c07737b7"
  dependencies:
    bluebird "^3.4.6"
    debug "^2.6.9"

safe-buffer@^5.0.1, safe-buffer@~5.1.0, safe-buffer@~5.1.1:
  version "5.1.1"
  resolved "https://registry.yarnpkg.com/safe-buffer/-/safe-buffer-5.1.1.tgz#893312af69b2123def71f57889001671eeb2c853"

safer-buffer@^2.1.0:
  version "2.1.2"
  resolved "https://registry.yarnpkg.com/safer-buffer/-/safer-buffer-2.1.2.tgz#44fa161b0187b9549dd84bb91802f9bd8385cd6a"

semver@^5.5.0:
  version "5.5.0"
  resolved "https://registry.yarnpkg.com/semver/-/semver-5.5.0.tgz#dc4bbc7a6ca9d916dee5d43516f0092b58f7b8ab"

seq-queue@0.0.5:
  version "0.0.5"
  resolved "https://registry.yarnpkg.com/seq-queue/-/seq-queue-0.0.5.tgz#d56812e1c017a6e4e7c3e3a37a1da6d78dd3c93e"

sequelize@^4.37.6:
  version "4.37.6"
  resolved "https://registry.yarnpkg.com/sequelize/-/sequelize-4.37.6.tgz#f37eec55a285cfdeab27f8a85c5db38034f87126"
  dependencies:
    bluebird "^3.5.0"
    cls-bluebird "^2.1.0"
    debug "^3.1.0"
    depd "^1.1.0"
    dottie "^2.0.0"
    generic-pool "^3.4.0"
    inflection "1.12.0"
    lodash "^4.17.1"
    moment "^2.20.0"
    moment-timezone "^0.5.14"
    retry-as-promised "^2.3.2"
    semver "^5.5.0"
    terraformer-wkt-parser "^1.1.2"
    toposort-class "^1.0.1"
    uuid "^3.2.1"
    validator "^9.4.1"
    wkx "^0.4.1"

shimmer@^1.1.0:
  version "1.2.0"
  resolved "https://registry.yarnpkg.com/shimmer/-/shimmer-1.2.0.tgz#f966f7555789763e74d8841193685a5e78736665"

sqlstring@2.3.1:
  version "2.3.1"
  resolved "https://registry.yarnpkg.com/sqlstring/-/sqlstring-2.3.1.tgz#475393ff9e91479aea62dcaf0ca3d14983a7fb40"

string_decoder@~1.0.3:
  version "1.0.3"
  resolved "https://registry.yarnpkg.com/string_decoder/-/string_decoder-1.0.3.tgz#0fc67d7c141825de94282dd536bec6b9bce860ab"
  dependencies:
    safe-buffer "~5.1.0"

terraformer-wkt-parser@^1.1.2:
  version "1.1.2"
  resolved "https://registry.yarnpkg.com/terraformer-wkt-parser/-/terraformer-wkt-parser-1.1.2.tgz#336a0c8fc82094a5aff83288f69aedecd369bf0c"
  dependencies:
    terraformer "~1.0.5"

terraformer@~1.0.5:
  version "1.0.8"
  resolved "https://registry.yarnpkg.com/terraformer/-/terraformer-1.0.8.tgz#51e0ad89746fcf2161dc6f65aa70e42377c8b593"
  dependencies:
    "@types/geojson" "^1.0.0"

toposort-class@^1.0.1:
  version "1.0.1"
  resolved "https://registry.yarnpkg.com/toposort-class/-/toposort-class-1.0.1.tgz#7ffd1f78c8be28c3ba45cd4e1a3f5ee193bd9988"

util-deprecate@~1.0.1:
  version "1.0.2"
  resolved "https://registry.yarnpkg.com/util-deprecate/-/util-deprecate-1.0.2.tgz#450d4dc9fa70de732762fbd2d4a28981419a0ccf"

uuid@^3.2.1:
  version "3.2.1"
  resolved "https://registry.yarnpkg.com/uuid/-/uuid-3.2.1.tgz#12c528bb9d58d0b9265d9a2f6f0fe8be17ff1f14"

validator@^9.4.1:
  version "9.4.1"
  resolved "https://registry.yarnpkg.com/validator/-/validator-9.4.1.tgz#abf466d398b561cd243050112c6ff1de6cc12663"

wkx@^0.4.1:
  version "0.4.4"
  resolved "https://registry.yarnpkg.com/wkx/-/wkx-0.4.4.tgz#cf751b672e4b45e162f9fd30124878e73d96c9b2"
  dependencies:
    "@types/node" "*"

yallist@^2.1.2:
  version "2.1.2"
  resolved "https://registry.yarnpkg.com/yallist/-/yallist-2.1.2.tgz#1c11f9218f076089a47dd512f93c6699a6a81d52"