postgresql Sequelize 配置以检索包含详细信息的总数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/47546824/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sequelize configuration to retrieve total count with details
提问by Jamsheer
I am working with node sequelizewith postgresdatabase. 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
我正在使用postgres数据库处理节点续集。我正在将分页记录加载到我的 UI，现在我需要使用我用来检索分页记录的相同查询来获取总记录数。任何人，请提供示例 sequelize 配置以执行相同操作。请参阅我预期的示例 postgres 查询以澄清我的问题
SELECT count(*) over() as total ,name FROM students WHERE gender='male' LIMIT 2
Thanks in advance
提前致谢
回答by redgeoff
If you want to avoid specifying SQL, you can also use findAndCountAll
如果你想避免指定 SQL，你也可以使用 findAndCountAll
回答by Adrien De Peretti
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.
您不能使用 sequelize 来做到这一点，但您可以通过 2 个单独的查询来完成，一个用于获取您需要的数据，另一个用于获取总数。
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 querylike that:
如果你想在一个查询中做到这一点，这可能不是最好的方式（因为你为每个结果添加元数据，而不是与模型相关的元数据），你可以创建一个raw query这样的：
await sequelize.query('select count(*) over(), name from table where condition', { model: Model });
I hope my explanation will help you :),
我希望我的解释能帮助你:),
Have a nice day!
祝你今天过得愉快！
回答by Yuriy Rypka
You can use findAndCountAllfor this purpose.
您可以findAndCountAll为此目的使用。
findAndCountAll - Search for multiple elements in the database, returns both data and total count
findAndCountAll - 在数据库中搜索多个元素，返回数据和总数
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 };
}
This uses ES6 destructuring and spread operators and async/awaitsyntax.
这使用 ES6 解构和扩展运算符和async/await语法。
paramsis an object with limit, and offsetproperties which overwrite default limit and offset.
params是一个具有limit, 和offset覆盖默认限制和偏移量的属性的对象。
Note that you may need to pass distinct: trueas well in case you includeother models in a query.
请注意，distinct: true如果您include在查询中使用其他模型，您可能也需要通过。
