Sequelize configuration to retrieve total count with details

Question

I am working with node sequelize with postgres database. I am loading paginated records to my UI, now I need to get total records count with the same query which I am using to retrieve paginate records. Anyone, please give the sample sequelize configuration to do the same. Please see my expected sample postgres query to clarify my question

SELECT count(*) over() as total ,name FROM students  WHERE gender='male' LIMIT 2 

Thanks in advance

Answer 1

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

Answer 2

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!

Answer 3

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.

Answer 4

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