node.js - 引用关系上的嵌套包含在 sequelize 中不起作用

Question

没有过滤评论，这些评论仅由用户的关注添加。我正在收到所有评论。大约有 11 条评论，只有 8 条是由用户的关注添加的，我想要 8 条但得到 11 条。
如果我从包含的模型中删除 required ，我会得到所有结果，因为然后内部查询被删除，但是如果我保留它们，我会得到如下解释的错误:

// user model
const User = sequelize.define('User', {
    userId: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true,
        field: 'user_id'
    },
    active: {
        type: DataTypes.INTEGER,
        defaultValue: 1
    },
}, {
    tableName: 'users',
    timestamps: true,
    paranoid: true
});

//Associations
User.associate = function(models) {
    models.User.belongsToMany(models.User, {
        through: 'Follower',
        foreignKey: 'followingUserId',
        otherKey: 'followedUserId',
        as: 'UserFollower'
    });
};

// follower model
const Follower = sequelize.define('Follower', {
    followingUserId: {
        type: DataTypes.INTEGER,
        allowNull: false,
        field: 'following_user_id'
    },
    followedUserId: {
        type: DataTypes.INTEGER,
        allowNull: false,
        field: 'followed_user_id'
    }
}, {
    tableName: 'follower'
});

// review model
const RestaurantReview = sequelize.define('RestaurantReview', {
    reviewId: {
        type: DataTypes.INTEGER,
        allowNull: false,
        primaryKey: true,
        field: 'review_id',
        autoIncrement: true
    },
    review: {
        type: DataTypes.TEXT,
        allowNull: true,
        field: 'review'
    },
    userId: {
        type: DataTypes.INTEGER,
        allowNull: true,
        field: 'user_id'
    },
}, {
    tableName: 'restaurant_review',
    timestamps: true,
    paranoid: true,

});

//Query I am firing is :

let queryParameters = {
    distinct: true,
    include: [{
        required: true,
        model: User,
        attributes: ['userId','active'],
        include: [{
            required: true,
            model: User,
            attributes: ['userId' ,'active'],
            as: 'UserFollower',
            through: {
                where: { followingUserId: userId },
            }
        }]
    }],
    offset: offsetValue,
    limit: limitValue
};
// END All the query parameters

const reviewsObject = await reviewModel.findAndCountAll( queryParameters );

//我得到的查询是:

SELECT "RestaurantReview".*, "User->UserFollower"."user_id" AS "User.UserFollower.userId", "User->UserFollower"."active" AS "User.UserFollower.active", "User->UserFollower"."lastMealtime" AS "User.UserFollower.lastMealtime", "User->UserFollower"."userName" AS "User.UserFollower.userName", "User->UserFollower"."email" AS "User.UserFollower.email", "User->UserFollower"."password" AS "User.UserFollower.password", "User->UserFollower"."lastvistDate" AS "User.UserFollower.lastvistDate", "User->UserFollower"."locationId" AS "User.UserFollower.locationId", "User->UserFollower"."socialId" AS "User.UserFollower.socialId", "User->UserFollower"."socialProvider" AS "User.UserFollower.socialProvider", "User->UserFollower"."socialPhoto" AS "User.UserFollower.socialPhoto", "User->UserFollower"."isLoggedIn" AS "User.UserFollower.isLoggedIn", "User->UserFollower"."createdAt" AS "User.UserFollower.createdAt", "User->UserFollower"."updatedAt" AS "User.UserFollower.updatedAt", "User->UserFollower"."deletedAt" AS "User.UserFollower.deletedAt", "User->UserFollower->Follower"."following_user_id" AS "User.UserFollower.Follower.followingUserId", "User->UserFollower->Follower"."followed_user_id" AS "User.UserFollower.Follower.followedUserId", "User->UserFollower->Follower"."createdAt" AS "User.UserFollower.Follower.createdAt", "User->UserFollower->Follower"."updatedAt" AS "User.UserFollower.Follower.updatedAt" FROM (SELECT "RestaurantReview"."review_id" AS "reviewId", "RestaurantReview"."review", "RestaurantReview"."user_id" AS "userId", "RestaurantReview"."restaurant_id" AS "restaurantId", "RestaurantReview"."rating", "RestaurantReview"."approved", "RestaurantReview"."abused", "RestaurantReview"."createdAt", "RestaurantReview"."updatedAt", "RestaurantReview"."deletedAt", "User"."user_id" AS "User.userId", "User"."active" AS "User.active" FROM "restaurant_review" AS "RestaurantReview" INNER JOIN "users" AS "User" ON "RestaurantReview"."user_id" = "User"."user_id" AND ("User"."deletedAt" > '2019-07-06 23:00:03.855 +00:00' OR "User"."deletedAt" IS NULL) WHERE ("RestaurantReview"."deletedAt" > '2019-07-06 23:00:03.855 +00:00' OR "RestaurantReview"."deletedAt" IS NULL) AND ( SELECT "User"."userId" FROM "users" AS "User" INNER JOIN ( "follower" AS "UserFollower->Follower" INNER JOIN "users" AS "UserFollower" ON "UserFollower"."user_id" = "UserFollower->Follower"."followed_user_id" AND "UserFollower->Follower"."following_user_id" = 16) ON "User"."user_id" = "UserFollower->Follower"."following_user_id" AND ("UserFollower"."deletedAt" > '2019-07-06 23:00:03.855 +00:00' OR "UserFollower"."deletedAt" IS NULL) WHERE "RestaurantReview"."user_id" = "User"."userId" LIMIT 1 ) IS NOT NULL LIMIT 12 OFFSET 0) AS "RestaurantReview" INNER JOIN ( "follower" AS "User->UserFollower->Follower" INNER JOIN "users" AS "User->UserFollower" ON "User->UserFollower"."user_id" = "User->UserFollower->Follower"."followed_user_id" AND "User->UserFollower->Follower"."following_user_id" = 16) ON "User.userId" = "User->UserFollower->Follower"."following_user_id" AND ("User->UserFollower"."deletedAt" > '2019-07-06 23:00:03.855 +00:00' OR "User->UserFollower"."deletedAt" IS NULL);

大约有 11 条评论，只有 8 条是由用户的关注添加的，我想要 8 条但得到 11 条。

错误:

Debug: handler, error SequelizeDatabaseError: column User.userId does not exist at Query.formatError (/Users/gagankaur/Documents/projects/Save-Earth/backend/node_modules/sequelize/lib/dialects/postgres/query.js:363:16) at query.catch.err (/Users/gagankaur/Documents/projects/Save-Earth/backend/node_modules/sequelize/lib/dialects/postgres/query.js:86:18) at tryCatcher (/Users/gagankaur/Documents/projects/Save-Earth/backend/node_modules/bluebird/js/release/util.js:16:23) at Promise._settlePromiseFromHandler (/Users/gagankaur/Documents/projects/Save-Earth/backend/node_modules/bluebird/js/release/promise.js:512:31) at Promise._settlePromise (/Users/gagankaur/Documents/projects/Save-Earth/backend/node_modules/bluebird/js/release/promise.js:569:18) at Promise._settlePromise0 (/Users/gagankaur/Documents/projects/Save-Earth/backend/node_modules/bluebird/js/release/promise.js:614:10) at Promise._settlePromises (/Users/gagankaur/Documents/projects/Save-Earth/backend/node_modules/bluebird/js/release/promise.js:689:18) at Async._drainQueue (/Users/gagankaur/Documents/projects/Save-Earth/backend/node_modules/bluebird/js/release/async.js:133:16) at Async._drainQueues (/Users/gagankaur/Documents/projects/Save-Earth/backend/node_modules/bluebird/js/release/async.js:143:10) at Immediate.Async.drainQueues (/Users/gagankaur/Documents/projects/Save-Earth/backend/node_modules/bluebird/js/release/async.js:17:14) at runCallback (timers.js:794:20) at tryOnImmediate (timers.js:752:5) at processImmediate [as _immediateCallback] (timers.js:729:5) Debug: internal, error

你能想到我做错了什么吗？提前致谢。

Answer 1

我只是使用以下参数来修复它。

let queryParameters = {
    distinct: true,
    include: [{
        duplicating: false,
        required: true,
        model: User,
        attributes: ['userId','active'],
        include: [{
            model: User,
            attributes: ['userId','active'],
            as: 'UserFollower',
            through: {
                where: { followingUserId: userId },
            },
            duplicating: false,
            required: true,

        }]
    }],
    offset: offsetValue,
    limit: limitValue
};

幸运的是它正在工作。我得到了正确的查询。
SELECT "RestaurantReview"."review_id"AS "reviewId", "RestaurantReview"."review", "RestaurantReview"."user_id"AS "userId", "RestaurantReview"."restaurant_id"AS "restaurantId", "RestaurantReview"。评级”，“RestaurantReview”。“批准”，“RestaurantReview”。“滥用”，“RestaurantReview”。“createdAt”，“RestaurantReview”。“updatedAt”，“RestaurantReview”。“deletedAt”，“用户”。“user_id” AS "User.userId", "User"."active"AS "User.active", "User->UserFollower"."user_id"AS "User.UserFollower.userId", "User->UserFollower"."active"AS "User.UserFollower.active", "User->UserFollower->Follower"."following_user_id"AS "User.UserFollower.Follower.followingUserId", "User->UserFollower->Follower"."followed_user_id"AS "用户。 UserFollower.Follower.followedUserId", "User->UserFollower->Follower"."createdAt"AS "User.UserFollower.Follower.createdAt", "User->UserFollower->Follower"."updatedAt"AS "User.UserFollower. Follower.updatedAt"FROM "restaurant_review"AS "RestaurantReview"INNER JOIN "users"AS "User"ON "RestaurantReview"."user_id"= "User"."user_id"AND ("User"."deletedAt"> '2019-07-08 07:08:00.264 +00:00' OR "User"."deletedAt"IS NULL) INNER JOIN ( "follower"AS "User->UserFollower->Follower"INNER JOIN "users"AS "User->UserFollower"ON "User->UserFollower"."user_id"= "User->UserFollower-> Follower"."followed_user_id"AND "User->UserFollower->Follower"."following_user_id"= 4) ON "User"."user_id"= "User->UserFollower->Follower"."following_user_id"AND ("User- >UserFollower"."deletedAt"> '2019-07-08 07:08:00.264 +00:00' OR "User->UserFollower"."deletedAt"IS NULL) WHERE ("RestaurantReview"."deletedAt"> '2019 -07-08 07:08:00.264 +00:00' 或 "RestaurantReview"."deletedAt"为空)