Sequelize configuration to retrieve total count with details

If you want to avoid specifying SQL, you can also use findAndCountAll

You can't do that with sequelize, but you can do through 2 separate queries, one to get the data you need and the other one to get the total count.

first one :

await Model.findAll({ where: { columnName: condition }});

second one :

await Model.count({ where: { columnName: condition }});

if you want to do that in one query which is maybe not the best way (because you adding metadata for each result which is not metadata related to the model) you can create a raw query like that:

await sequelize.query('select count(*) over(), name from table where condition', { model: Model });

I hope my explanation will help you :),

Have a nice day!

You can count data in two ways

1. With data (findAndCountAll)
2. Without Data (count)

1: With data

const students = await students.findAndCountAll({
  where: {
     gender = "male"
  }
});
console.log(students)

It will return Count students where gender is male and data of that students

2: Without Data

const students = await students.count({
  where: {
     gender = "male"
  }
});
console.log(students)

It will return only Count students where gender is male

You can use findAndCountAll for this purpose.

findAndCountAll - Search for multiple elements in the database, returns both data and total count

Here is an example:

const getStudents = async params => {
  const { count, rows: students } = await Student.findAndCountAll({
    where: {
      gender: 'male',
    },
    limit: DEFAULT_PAGE_SIZE,
    order: [['id', 'ASC']],
    ...params,
  });

  return { count, students };
}

params is an object with limit and offset properties that overwrite default limit and offset.

Note that you may need to pass distinct: true as well in case you include other models in a query.