sql - Sequelize 一对多关联错误-6ren

sql - Sequelize 一对多关联错误

转载作者：行者123 更新时间：2023-11-29 12:24:28

我目前正在开发一个新的 API，它将 SequelizeJS 与 PostgreDB 结合使用。我通常使用 MongoDB，但在让关联基于特定列(而非标准 ID)工作时遇到了一些麻烦。我想减少在常见操作中调用数据库的次数。

我有 3 个表，名称如下:Sites、Buildings、Areas。
- 站点有许多建筑物(站点:引用 <->建筑物:siteReference)
- 建筑有很多区域(建筑:引用 <-> 区域:建筑引用)
- 建筑属于 field
- 区域属于建筑物

我已经尽我所能地遵循了 Sequelize 文档(发现理解示例有点困难)，并且相信以下内容会起作用(目前只是站点 <-> 建筑物):

Sites.hasMany(Buildings, { as: 'relatedBuildings', targetKey: 'siteReference'});
Buildings.belongsTo(Sites, { as: 'parentSite', sourceKey: 'reference'});

// Query function
return mainDB.Sites
    .findAll({
        raw: true,
        include: [{
            model: mainDB.Buildings,
            as: 'relatedBuildings'
        }]
    })
    .then(dbRes => { console.log(dbRes));
    .catch(error => {console.log('ERROR', error.message)})

但是，当我运行上面的代码时，我收到以下错误:

First Error: relation "Sites" does not exist (note: the associations are stopping this table from being created)

Second Error: 
"status": 409,
            "message": "A database error occurred. Further details attached.",
            "value": {
                "name": "SequelizeDatabaseError",
                "parent": {
                    "name": "error",
                    "length": 110,
                    "severity": "ERROR",
                    "code": "42P01",
                    "position": "1678",
                    "file": "parse_relation.c",
                    "line": "1160",
                    "routine": "parserOpenTable",
                    "sql": "SELECT \"Sites\".\"id\", \"Sites\".\"reference\", \"Sites\".\"name\", \"Sites\".\"description\", \"Sites\".\"addressLine1\", \"Sites\".\"addressLine2\", \"Sites\".\"city\", \"Sites\".\"county\", \"Sites\".\"postcode\", \"Sites\".\"country\", \"Sites\".\"lat\", \"Sites\".\"long\", \"Sites\".\"type\", \"Sites\".\"companyReference\", \"Sites\".\"namedContactReference\", \"Sites\".\"status\", \"Sites\".\"created_at\", \"Sites\".\"updated_at\", \"relatedBuildings\".\"id\" AS \"relatedBuildings.id\", \"relatedBuildings\".\"reference\" AS \"relatedBuildings.reference\", \"relatedBuildings\".\"name\" AS \"relatedBuildings.name\", \"relatedBuildings\".\"description\" AS \"relatedBuildings.description\", \"relatedBuildings\".\"addressLine1\" AS \"relatedBuildings.addressLine1\", \"relatedBuildings\".\"addressLine2\" AS \"relatedBuildings.addressLine2\", \"relatedBuildings\".\"city\" AS \"relatedBuildings.city\", \"relatedBuildings\".\"county\" AS \"relatedBuildings.county\", \"relatedBuildings\".\"postcode\" AS \"relatedBuildings.postcode\", \"relatedBuildings\".\"country\" AS \"relatedBuildings.country\", \"relatedBuildings\".\"lat\" AS \"relatedBuildings.lat\", \"relatedBuildings\".\"long\" AS \"relatedBuildings.long\", \"relatedBuildings\".\"type\" AS \"relatedBuildings.type\", \"relatedBuildings\".\"siteReference\" AS \"relatedBuildings.siteReference\", \"relatedBuildings\".\"namedContactReference\" AS \"relatedBuildings.namedContactReference\", \"relatedBuildings\".\"status\" AS \"relatedBuildings.status\", \"relatedBuildings\".\"created_at\" AS \"relatedBuildings.created_at\", \"relatedBuildings\".\"updated_at\" AS \"relatedBuildings.updated_at\", \"relatedBuildings\".\"site_id\" AS \"relatedBuildings.site_id\", \"relatedBuildings\".\"related_buildings_id\" AS \"relatedBuildings.related_buildings_id\" FROM \"Sites\" AS \"Sites\" LEFT OUTER JOIN \"Buildings\" AS \"relatedBuildings\" ON \"Sites\".\"id\" = \"relatedBuildings\".\"site_id\";"
                },
                "original": {
                    "name": "error",
                    "length": 110,
                    "severity": "ERROR",
                    "code": "42P01",
                    "position": "1678",
                    "file": "parse_relation.c",
                    "line": "1160",
                    "routine": "parserOpenTable",
                    "sql": "SELECT \"Sites\".\"id\", \"Sites\".\"reference\", \"Sites\".\"name\", \"Sites\".\"description\", \"Sites\".\"addressLine1\", \"Sites\".\"addressLine2\", \"Sites\".\"city\", \"Sites\".\"county\", \"Sites\".\"postcode\", \"Sites\".\"country\", \"Sites\".\"lat\", \"Sites\".\"long\", \"Sites\".\"type\", \"Sites\".\"companyReference\", \"Sites\".\"namedContactReference\", \"Sites\".\"status\", \"Sites\".\"created_at\", \"Sites\".\"updated_at\", \"relatedBuildings\".\"id\" AS \"relatedBuildings.id\", \"relatedBuildings\".\"reference\" AS \"relatedBuildings.reference\", \"relatedBuildings\".\"name\" AS \"relatedBuildings.name\", \"relatedBuildings\".\"description\" AS \"relatedBuildings.description\", \"relatedBuildings\".\"addressLine1\" AS \"relatedBuildings.addressLine1\", \"relatedBuildings\".\"addressLine2\" AS \"relatedBuildings.addressLine2\", \"relatedBuildings\".\"city\" AS \"relatedBuildings.city\", \"relatedBuildings\".\"county\" AS \"relatedBuildings.county\", \"relatedBuildings\".\"postcode\" AS \"relatedBuildings.postcode\", \"relatedBuildings\".\"country\" AS \"relatedBuildings.country\", \"relatedBuildings\".\"lat\" AS \"relatedBuildings.lat\", \"relatedBuildings\".\"long\" AS \"relatedBuildings.long\", \"relatedBuildings\".\"type\" AS \"relatedBuildings.type\", \"relatedBuildings\".\"siteReference\" AS \"relatedBuildings.siteReference\", \"relatedBuildings\".\"namedContactReference\" AS \"relatedBuildings.namedContactReference\", \"relatedBuildings\".\"status\" AS \"relatedBuildings.status\", \"relatedBuildings\".\"created_at\" AS \"relatedBuildings.created_at\", \"relatedBuildings\".\"updated_at\" AS \"relatedBuildings.updated_at\", \"relatedBuildings\".\"site_id\" AS \"relatedBuildings.site_id\", \"relatedBuildings\".\"related_buildings_id\" AS \"relatedBuildings.related_buildings_id\" FROM \"Sites\" AS \"Sites\" LEFT OUTER JOIN \"Buildings\" AS \"relatedBuildings\" ON \"Sites\".\"id\" = \"relatedBuildings\".\"site_id\";"
                },
                "sql": "SELECT \"Sites\".\"id\", \"Sites\".\"reference\", \"Sites\".\"name\", \"Sites\".\"description\", \"Sites\".\"addressLine1\", \"Sites\".\"addressLine2\", \"Sites\".\"city\", \"Sites\".\"county\", \"Sites\".\"postcode\", \"Sites\".\"country\", \"Sites\".\"lat\", \"Sites\".\"long\", \"Sites\".\"type\", \"Sites\".\"companyReference\", \"Sites\".\"namedContactReference\", \"Sites\".\"status\", \"Sites\".\"created_at\", \"Sites\".\"updated_at\", \"relatedBuildings\".\"id\" AS \"relatedBuildings.id\", \"relatedBuildings\".\"reference\" AS \"relatedBuildings.reference\", \"relatedBuildings\".\"name\" AS \"relatedBuildings.name\", \"relatedBuildings\".\"description\" AS \"relatedBuildings.description\", \"relatedBuildings\".\"addressLine1\" AS \"relatedBuildings.addressLine1\", \"relatedBuildings\".\"addressLine2\" AS \"relatedBuildings.addressLine2\", \"relatedBuildings\".\"city\" AS \"relatedBuildings.city\", \"relatedBuildings\".\"county\" AS \"relatedBuildings.county\", \"relatedBuildings\".\"postcode\" AS \"relatedBuildings.postcode\", \"relatedBuildings\".\"country\" AS \"relatedBuildings.country\", \"relatedBuildings\".\"lat\" AS \"relatedBuildings.lat\", \"relatedBuildings\".\"long\" AS \"relatedBuildings.long\", \"relatedBuildings\".\"type\" AS \"relatedBuildings.type\", \"relatedBuildings\".\"siteReference\" AS \"relatedBuildings.siteReference\", \"relatedBuildings\".\"namedContactReference\" AS \"relatedBuildings.namedContactReference\", \"relatedBuildings\".\"status\" AS \"relatedBuildings.status\", \"relatedBuildings\".\"created_at\" AS \"relatedBuildings.created_at\", \"relatedBuildings\".\"updated_at\" AS \"relatedBuildings.updated_at\", \"relatedBuildings\".\"site_id\" AS \"relatedBuildings.site_id\", \"relatedBuildings\".\"related_buildings_id\" AS \"relatedBuildings.related_buildings_id\" FROM \"Sites\" AS \"Sites\" LEFT OUTER JOIN \"Buildings\" AS \"relatedBuildings\" ON \"Sites\".\"id\" = \"relatedBuildings\".\"site_id\";"
            }

最终，当我在 Sites 表上运行 FindAll() 时，我的目标是获得如下输出:

Site
- Building
- - Area
- - Area
- Building
- - Area
- Building
- - Area
- - Area
- - Area
Site
- Building
- - Area
- - Area
- Building
- - Area
- - Area
Site
- Building
- - Area
- - Area

我哪里错了？

更新 - 这是我的模型(注意我在别处正式定义它们)

网站

const Sites = {
    name: 'Sites',
    schema: {
        id: {
            type: DataTypes.INTEGER,
            primaryKey: true,
            autoIncrement: true,
            allowNull: false
        },
        reference: {
            type: DataTypes.STRING(100),
            unique: true,
            allowNull: false
        },
        name: {
            type: DataTypes.STRING,
            allowNull: false
        },
        description: {
            type: DataTypes.STRING(512),
            allowNull: true
        },
        addressLine1: {
            type: DataTypes.STRING,
            allowNull: false
        },
        addressLine2: {
            type: DataTypes.STRING,
            allowNull: false
        },
        city: {
            type: DataTypes.STRING,
            allowNull: false
        },
        county: {
            type: DataTypes.STRING,
            allowNull: false
        },
        postcode: {
            type: DataTypes.STRING,
            allowNull: false
        },
        country: {
            type: DataTypes.STRING,
            allowNull: false
        },
        lat: {
            type: DataTypes.STRING,
            allowNull: false
        },
        long: {
            type: DataTypes.STRING,
            allowNull: false
        },
        type: {
            type: DataTypes.STRING,
            allowNull: false
        },
        companyReference: {
            type: DataTypes.STRING(100),
            allowNull: false
        },
        namedContactReference: {
            type: DataTypes.STRING(100),
            allowNull: true
        },
        status: {
            type: DataTypes.STRING(20),
            allowNull: false
        }
    },
    options: {
        underscored: true
    }
};

export default Sites;

建筑物

const Buildings = {
    name: 'Buildings',
    schema: {
        id: {
            type: DataTypes.INTEGER,
            primaryKey: true,
            autoIncrement: true,
            allowNull: false
        },
        reference: {
            type: DataTypes.STRING(100),
            unique: true,
            allowNull: false
        },
        name: {
            type: DataTypes.STRING,
            allowNull: false
        },
        description: {
            type: DataTypes.STRING(256),
            unique: false,
            allowNull: true
        },
        addressLine1: {
            type: DataTypes.STRING,
            allowNull: false
        },
        addressLine2: {
            type: DataTypes.STRING,
            allowNull: false
        },
        city: {
            type: DataTypes.STRING,
            allowNull: false
        },
        county: {
            type: DataTypes.STRING,
            allowNull: false
        },
        postcode: {
            type: DataTypes.STRING,
            allowNull: false
        },
        country: {
            type: DataTypes.STRING,
            allowNull: false
        },
        lat: {
            type: DataTypes.STRING,
            allowNull: false
        },
        long: {
            type: DataTypes.STRING,
            allowNull: false
        },
        type: {
            type: DataTypes.STRING,
            allowNull: false
        },
        siteReference: {
            type: DataTypes.STRING(100),
            allowNull: false
        },
        namedContactReference: {
            type: DataTypes.STRING(100),
            allowNull: true
        },
        status: {
            type: DataTypes.STRING(20),
            allowNull: false
        }
    },
    options: { underscored: true }
};
export default Buildings;

区域

const Areas = {
    name: 'Areas',
    schema: {
        id: {
            type: DataTypes.INTEGER,
            primaryKey: true,
            autoIncrement: true,
            allowNull: false
        },
        reference: {
            type: DataTypes.STRING(100),
            unique: true,
            allowNull: false
        },
        buildingReference: {
            type: DataTypes.STRING(100),
            unique: false,
            allowNull: false
        },
        panelReference: {
            type: DataTypes.STRING(100),
            unique: false,
            allowNull: false
        },
        name: {
            type: DataTypes.STRING(128),
            unique: false,
            allowNull: false
        },
        description: {
            type: DataTypes.STRING(512),
            allowNull: true
        },
        drawingReference: {
            type: DataTypes.STRING(100),
            unique: false,
            allowNull: false
        },
        status: {
            type: DataTypes.STRING(10),
            allowNull: false
        }
    },
    options: { underscored: true }
};
export default Areas;

最佳答案

您是否在模型中设置了关联？

Please refer to the Sequelize docs at the section belongs-to-many-associations

例子

如果我对你的问题的理解正确，你想建立一个多对多的关系，换句话说就是 n:m。

使用 sequelize你可以通过在两个模型上设置一个关系来做到这一点

belongsToMany(Model,{through: 'tableNameYouWant'});

// In Your Site Model
Sites.belongsToMany(Buildings, {through: 'SiteBuildings'});

// In your Building Model
Buildings.belongsToMany(Sites, {through: 'SiteBuildings'});

// In your Area Model
Areas.belongsTo(Buildings)

如果这有助于解决您的问题，请告诉我!

关于sql - Sequelize 一对多关联错误，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/48107278/

文章推荐： php - 如何获取表中的所有唯一名称并回显它们？

文章推荐： php - 该 MySQL 版本不允许使用该命令

文章推荐： php - 针对多个 MYSQL 列查询 PHP 变量

npm 安装不起作用 | npm 错误!路径 | npm 错误!代码 | npm 错误!错误 | npm 错误!系统调用 | npm 错误!恩恩特
我已经使用 vue-cli 两个星期了，直到今天一切正常。我在本地建立这个项目。 https://drive.google.com/open?id=0BwGw1zyyKjW7S3RYWXRaX24tQ
python - pytesseract 错误 Windows 错误 [错误 2]
您好，我正在尝试使用 python 库 pytesseract 从图像中提取文本。请找到代码: from PIL import Image from pytesseract import image_
C 错误 TLS 错误
我的错误 /usr/bin/ld: errno: TLS definition in /lib/libc.so.6 section .tbss mismatches non-TLS reference
r - 错误 `contrasts' 错误
我已经训练了一个模型，我正在尝试使用 predict函数但它返回以下错误。 Error in contrasts<-(*tmp*, value = contr.funs[1 + isOF[nn]])
postgresql - PowerBI 直接查询连接到 PostgreSQL 错误。 OLE 或 ODBC 错误 : [Expression. 错误] 我们无法将表达式折叠到数据源
根据Microsoft DataConnectors的信息我想通过 this ODBC driver 创建一个从 PowerBi 到 PostgreSQL 的连接器使用直接查询。我重用了 Micros
java - Android MediaPlayer 错误(在状态 1 中开始调用；错误 (-38, 0)；错误 (-38,0))
我已经为 SoundManagement 创建了一个包，其中有一个扩展 MediaPlayer 的类。我希望全局控制这个变量。这是我的代码: package soundmanagement; impo
heroku - PG::错误:错误:Heroku的内存不足
我在Heroku上部署了一个应用程序。我正在使用免费服务。我经常收到以下错误消息。 PG::Error: ERROR: out of memory 如果刷新浏览器，就可以了。但是随后，它又随机发生
.htaccess - .htaccess 错误，错误 500
我正在运行 LAMP 服务器，这个 .htaccess 给我一个 500 错误。其作用是过滤关键字并重定向到相应的域名。 Options +FollowSymLinks RewriteEngine
robocopy 错误，错误 32 (0x00000020)
我有两个驱动器 A 和 B。使用 python 脚本，我在“A”驱动器中创建一些文件，并运行 powerscript，该脚本以 1 秒的间隔将驱动器 A 中的所有文件复制到驱动器 B。我在 powe
postgresql 错误 - 错误 : input is out of range
下面的函数一直返回这个错误信息。我认为可能是 double_precision 字段类型导致了这种情况，我尝试使用 CAST，但要么不是这样，要么我没有做对...帮助？这是错误: ERROR: i
mysql - 错误 1064 MySQL 错误
这个问题已经有答案了: Syntax error due to using a reserved word as a table or column name in MySQL (1 个回答) 已关闭
mysql - mysql 错误(错误 1136)
我的数据库有这个小问题。我创建了一个表“articoli”，其中包含商品的品牌、型号和价格。每篇文章都由一个 id (ID_ARTICOLO)` 定义，它是一个自动递增字段。好吧，现在当我尝试插
c++ - 错误 C2228、错误 C2275
我是新来的。我目前正在 DeVry 在线学习中级 C++ 编程。我们正在使用 C++ Primer Plus 这本书，到目前为止我一直做得很好。我的老师最近向我们扔了一个曲线球。我目前的任务是这样的:
c++ - 错误 LNK2019 错误 C++
这个问题在这里已经有了答案: What is an undefined reference/unresolved external symbol error and how do I fix it?
html - 奇怪的 IE7 错误/错误
我的网站中有一段代码有问题；此错误仅发生在 Internet Explorer 7 中。我没有在这里发布我所有的 HTML/CSS 标记，而是发布了网站的一个版本 here . 如您所见，我在列中有
node.js - 错误!错误 : EPERM,
如果尝试在 USB 设备上构建 node.js 应用程序时在我的树莓派上使用 npm 时遇到一些问题。 package.json 看起来像这样: { "name" : "node-todo",
python - 无 Python 错误/错误？
在 Python 中，您有 None单例，在某些情况下表现得很奇怪: >>> a = None >>> type(a) >>> isinstance(a,None) Traceback (most
java - Android Studio 错误 - 错误 :java. util.concurrent.ExecutionException : com. android.tools.aapt2.Aapt2Exception:AAPT 错误
这是我的 build.gradle (Module:app) 文件: apply plugin: 'com.android.application' android { compileSdkV
android - 任务 ':app:compileDebugJavaWithJavac' 执行失败。错误 :(2055, 52) 错误 : ';' expected Error:(2055, 59) 错误:<标识符> 预期
我是 android 的新手，我的项目刚才编译和运行正常，但在我尝试实现抽屉导航后，它给了我这个错误 FAILURE: Build failed with an exception. What wen
PHP 7.2.25 错误!= 错误？
谁能解释一下？我想我正在做一些非常愚蠢的事情，并且急切地等待着启蒙。我得到这个输出: phpversion() == 7.2.25-1+0~20191128.32+debian8~1.gbp108

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

sql - Sequelize 一对多关联错误

例子