CODEWITHSUNDEEP
node.jsdatabasepostgresqlsequelize.js
Jamsheer
Jamsheer

Reputation: 3753

Sequelize configuration to retrieve total count with details

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

Upvotes: 19

Views: 47557

Answers (4)

Aryan
Aryan

Reputation: 3636

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

Upvotes: 2

Yuriy Rypka
Yuriy Rypka

Reputation: 2107

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.

Upvotes: 12

redgeoff
redgeoff

Reputation: 3341

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

Upvotes: 43

Adrien De Peretti
Adrien De Peretti

Reputation: 3672

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!

Upvotes: 13

Related Questions