i am getting count as 1 instead of 0 when i did groupby using sequelize in nodejs

Question

I have two tables "project" and "company". I have created their models as 'Project' and 'Company' in sequelize.

Project table contains

project_id	company_id	project_name
1	1	project1
2	2	project2
3	2	project3

Company table contains

company_id	company_name
1	xyz
2	test
3	abc

my requirement is to get all companies in the Company table and the no of projects they have.

i have wrote the query in sequelize as

const result = await Company.findAll({
    attributes: [ ['company_id', 'id'] ,'company_name', [Sequelize.fn('COUNT', 'Project.project_id'), 'no_of_projects'] ],
    include: [{ model: Project, attributes: [] }],
    group: ['company_id'],
    order: [
        [sortBy, sortOrder]
    ],
    offset: index,
    limit: limit,
    subQuery: false
})

but i am getting no_of_projects as 1 instead of 0 for company "abc" as it doesn't have any project. I need to get no_of_projects as 0 if there is no project for the company. i am new to sql and sequelize. can anyone please help me in solving this. thanks in advance.

these are the associations

Company.associate = function(models) { Company.hasMany(models.Project, { foreignKey: "company_id" }) }

Project.associate = function(models) { Project.belongsTo(models.Company, { foreignKey: 'company_id', }) }

Have you defined the associations while creating the tables??.. with foreign key as company_id between projects and company — Sasi Kumar M, Jan 07 '21 at 12:25
Yes. i have defined. You can find the associations in the question. Just now i have edited it. — Praveen Reddy, Jan 07 '21 at 12:27
this https://stackoverflow.com/questions/20460270/how-to-make-join-queries-using-sequelize-on-node-js might help — Sasi Kumar M, Jan 07 '21 at 12:42

score 1 · Accepted Answer · edited Aug 10 '22 at 16:47

1

I have found the mistake in my sequelize query. Instead of

[Sequelize.fn('COUNT', 'Project.project_id')

It worked when I replace that with

[Sequelize.fn('COUNT', Sequelize.col('project_id'))

I need to specify that as a column to sequelize otherwise it is considering empty string as a value. That's why I got count as 1 instead of 0.

edited Aug 10 '22 at 16:47

ParisaN

1,816
2
23
55

answered Jan 10 '21 at 07:23

Praveen Reddy

33
1
9

score 0 · Answer 2 · answered Jan 07 '21 at 11:55

0

If you want SQL then you can use following:

select c.company_id, c.company_name, count(p.project_id) as num_of_project
  from company c left join project p on p.comapny_id = c.company_id
group by c.company_id, c.company_name

answered Jan 07 '21 at 11:55

Popeye

35,427
4
10
31

Thanks for the query. But i want the query in sequelize. – Praveen Reddy Jan 07 '21 at 12:12

i am getting count as 1 instead of 0 when i did groupby using sequelize in nodejs

2 Answers2