Блог

моя жалоба на модель:

 module.exports = (sequelize, DataTypes) => {
  class complaint extends Model {
    static associate(models) {
      this.research = this.hasMany(models.research, {as: 'research', foreignKey: 'complaint_id' })
    }
  }
  complaint
    .init({
            date: {
              type: DataTypes.DATE,
              allowNull: false,
            },
            complaint: {
              type: DataTypes.TEXT,
              allowNull: false,
            },
          }, {
            sequelize,
            modelName: 'complaint',
            freezeTableName: true,
            tableName: 'complaint',
          })
  return complaint
}
  

мое исследование модели:

 module.exports = (sequelize, DataTypes) => {
  class research extends Model {
    static associate(models) {
      this.complaint = this.belongsTo(models.complaint, { foreignKey: 'id'})
    }
  }
  research.init({
                  complaint_id: {
                    type: DataTypes.INTEGER,
                    model: 'complaint',
                    key: 'id',
                    deferrable: Deferrable.INITIALLY_IMMEDIATE(),
                  },
                  research: {
                    type: DataTypes.JSONB,
                    allowNull: false,
                  },
                }, {
                  sequelize,
                  modelName: 'research',
                  freezeTableName: true,
                  tableName: 'research',
                })
  return research
}
  

мой запрос

 const query = {
      where: {
        '$research.id$': null,
      },
      limit: 3,
      attributes: [
        'id',
        'date',
      ],
      include: [
        {
          model: models.research,
          as: 'research',
          attributes: ['id','complaint_id'],
        },
      ],
      order: [["date","ASC"]],
    }

const complaint = await models.complaint.findAll(query) // <--- error "missing FROM-clause entry for table "research""
  

если удалить «limit: 3», он работает нормально. также с limit: 3 генерируется странный sql:
без ограничения: 3

 SELECT "complaint"."id", "complaint"."date", "research"."id" AS "research.id"
FROM "complaint" AS "complaint"
         LEFT OUTER JOIN "research" AS "research" ON "complaint"."id" = "research"."complaint_id"
WHERE "complaint"."sub_id" IS NULL
  AND "research"."id" IS NULL           
ORDER BY "complaint"."date" ASC;

  

с ограничением: 3

 SELECT "complaint".*, "research"."id" AS "research.id"
FROM (SELECT "complaint"."id", "complaint"."date"
      FROM "complaint" AS "complaint"
      WHERE "complaint"."sub_id" IS NULL
        AND "research"."id" IS NULL
      ORDER BY "complaint"."date" ASC
      LIMIT 3) AS "complaint"
         LEFT OUTER JOIN "research" AS "research" ON "complaint"."id" = "research"."complaint_id"
ORDER BY "complaint"."date" ASC;
  

хотя ожидается, что только слово изменится с «research».»id» равно NULL на «research».»id» NotNull

Это ошибка в моем запросе или в коде продолжения?