Counting a column's data in Sequelize

Question

I have two tables associated with each other in Sequelize and I want to count the following

Jobs table columns: 'id', 'name'

worker table colmuns: 'id', 'name', 'job_id'

Lets say we have 5 jobs available right now and lots of workers associated with the job_id I want to show job's 'id', 'name' and count how many workers has the same job_id

Example: {id: 1, name: Engineer, count: 6}; this means in worker's table 6 workers has job_id = 1

My query is like this:

Jobs.findAll({
attribute:['id', 'name'],
include: [
    {
    model: Worker,
    attributes: [[Sequelize.fn('count', Sequelize.col('job_id')), 'count']]
    }
],
group: ['Job.id', 'Worker.id']
})

Gives me the following result without count:

[
   {
    "id": 7,
    "name": "Doctor",
    "users": []
   },
   {
    "id": 1,
    "name": "Engineer",
    "users": [
              {
               "count": 1
              },
              {
              "count": 1
              },
              {
              "count": 1
              },
              {
              "count": 1
              },
              {
              "count": 1
              },
              {
              "count": 1
              }
            ]
         },
     {
       "id": 4,
       "name": "Pilot",
       "users": []
     }
]

Whereas it should count all job_id of the users as shows as:

    "id": 1,
    "name": "Engineer",
    "count": 6

score 1 · Accepted Answer · answered Jun 11 '20 at 04:42

1

you have to define count in Job attributes like this .

Jobs.findAll({
    attribute: ['id', 'name', [sequelize.fn("COUNT", sequelize.col('Worker.job_id')), "count"]],
    include: [
        {
            model: Worker,
            attributes: []
        }
    ],
    group: ['Worker.job_id', 'Job.id']
})

hope this'll help you .

answered Jun 11 '20 at 04:42

Arpit Vyas

2,118
1
7
18

@Angels if this does ans to your que than please do upvote & consider this ans as marked .so can close this que . – Arpit Vyas Jun 12 '20 at 16:11

Counting a column's data in Sequelize

1 Answers1