sequelize findall with count every relation child for each parent

Question

and thank for the answers.

i use nodejs / sequelize / mysql

this is my problem:

i have table client like this:

id username age name   
----------------------
12 seveun   25  john  |
----------------------
10 superlol 12  johny |
----------------------

the table client has one to many relation with statistic table

the statistic table seem like this :

id  gain  profit clientId
-------------------------
12  22     25      12   |
-------------------------
10 34      12      12   |
-------------------------
10 34      12      10   |
-------------------------

i want this result :

{
 client: [
  {
    'username': 'seveun',
    'statistic': [total: 2]
  },
  {
    'username': 'superlol'
    'statistic': [total: 1]
  }
 ]
}

i test this sequelize code :

await ClientModel.findAll({
  include: [
   {
     as: 'statistic',
     model: StatisticModel,
     attributes: [
       [[Sequelize.fn("COUNT", Sequelize.col("statistic.id")), "total"]] 
     ],
   },
 ],
});

but the count, count every statistic, not statistic for each parent

my result is :

client: [
  {
    'username': 'seveun',
    'statistic': [total: 3]
  }

thanks a lot for your help

if the amount is counted over the whole table then you must tell that you need in grouping over username. I.e. add `group: ['username']`. — Akina, Mar 19 '20 at 09:41
Does this answer your question? [How does group by works in sequelize?](https://stackoverflow.com/questions/22627258/how-does-group-by-works-in-sequelize) — Akina, Mar 19 '20 at 09:44
thanks for your answer but no its not that because the result will be `client: [ [{ 'username': 'seveun', 'statistic': [total: 3] }, { username': 'superlol', 'statistic': [total: 3]}]` the count will count every statistic — Matehis Langlais, Mar 19 '20 at 12:41

ArtAbh · Answer 1 · 2020-09-01T17:04:12.483

This should work perfectly fine for 1:m associations. Lets you count children for every parent. But, for some reason, this doesn't work for multiple 'include's. So NOTE!!!:

trying to count grand-children breaks the entire query

trying to get attributes of children breaks the entire query

sequelize: 5.21.11

await ClientModel.findAll({
  include: [
    {
      as: 'statistic',
      model: StatisticModel,
      attributes: [], // empty array is important here!
     },
  ],
  attributes: [
    'id', [Sequelize.fn("COUNT", Sequelize.col("statistic.id")), "total"] 
  ],
  group : ['ClientModel.id'], // group only by parent.id here!
});

score 0 · Answer 2 · answered Mar 06 '21 at 07:46

Aggregation must be done on the parent model according to this issue

Try the following code for your use case

  await ClientModel.findAll({
    attributes: [
      "username",
      [Sequelize.fn("COUNT", Sequelize.col("statistic.id")), "total"],
    ],
    include: [
      {
        as: "statistic",
        model: StatisticModel,
        attributes: [],
      },
    ],
    group: ["ClientModel.id"],
  });

Sequelize >= 3.0.1

sequelize findall with count every relation child for each parent

2 Answers2