如何在sequelize中的子子子模型中添加条件,这会影响findAndCountAll中的父模型? [英] How do I add conditions in sub-sub child models in sequelize which should impact my parent Model in findAndCountAll?
问题描述
在续集中,我想在sub-sub子模型的findAndCountAll调用中添加一个条件.如果条件为假,它将影响父模型,并且findAndCountAll应该返回数组长度0.目前,仅该子-子子模型的数组长度为0,其余父对象将被返回.
In sequelize, I want to add a condition in findAndCountAll call in sub-sub child models. If the condition is false, it should impact on parent models and findAndCountAll should return array length 0. Currently, just that sub-sub child model's array length gets 0 and the rest of parents are being returned.
我在共享的代码中添加了更多详细信息. 我的代码是这样的:
I have added more details in the code I am sharing. My code is like this:
const { limit, offset } = dbHelpers.GetPagination(
req.query.limit,
req.query.offset
);
const results = await models.ModelA.findAndCountAll({
where: condition,
distinct: true,
limit,
offset,
order: [["id", "DESC"]],
include: [
{
model: models.ModelB,
as: "ModelB",
include: [
{
model: models.ModelC,
separate: true,
},
{
model: models.ModelD,
separate: true,
include: [
{
model: models.ModelE,
separate: true,
{
model: models.ModelF,
where: "I want to add my condition here which should impact the whole query if not found just like the condition I have added in ModelG",
separate: true,
},
],
},
],
},
],
},
{
model: models.ModelG,
where: "I have added a condition here and if nothing is found, my parent query returns nothing which is exactly what I want.",
include: [
{
model: models.ModelH,
as: "ModelH",
},
],
},
],
});
推荐答案
好的,我想出了解决方案,但是使用Joins添加了原始查询,在其中我添加了所需条件,并获取了主模型的id.然后,我使用sequelize在where子句中添加这些id来获取数据.我的代码现在看起来像这样.
Okay so I figured the solution but adding a raw query with Joins in which I added my required conditions wherever I needed and fetched id's of the main model. Then I fetched the data using sequelize adding those ids in where clause. My code looks like this now.
const { limit, offset } = dbHelpers.GetPagination(
req.query.limit,
req.query.offset
);
let querySting = "(SELECT count(DISTINCT p.id) FROM ModelA p " +
"JOIN ModelB cp ON cp.pId = p.id " +
"JOIN ModelC cs ON cs.cpId = cp.id " +
"JOIN ModelD ms ON ms.csId = cs.id " +
"LEFT JOIN ModelE sa ON sa.msId = ms.id " +
"LEFT JOIN ModelF pp ON pp.msId = ms.id " +
"LEFT JOIN ModelG sta ON sta.ppId = pp.id " +
"LEFT JOIN ModelH ol ON ol.cdId = cd.id " +
"WHERE " //you can add your conditions here
+ " ORDER BY p.id desc LIMIT " + offset + ", " + limit + ")"
const rawQuery = await models.sequelize.query(querySting,
{ type: QueryTypes.SELECT })
const Ids = rawQuery.map(result => result.id)
const results = await models.ModelA.findAndCountAll({
where: {
id: Ids
},
include: [
{
model: models.ModelB,
as: "ModelB",
include: [
{
model: models.ModelC,
separate: true,
},
{
model: models.ModelD,
separate: true,
include: [
{
model: models.ModelE,
separate: true,
{
model: models.ModelF,
separate: true,
},
],
},
],
},
],
},
{
model: models.ModelG,
include: [
{
model: models.ModelH,
as: "ModelH",
},
],
},
],
});
这篇关于如何在sequelize中的子子子模型中添加条件,这会影响findAndCountAll中的父模型?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!