mysql - 如何从关联表中调用字段数据？

Question

在带有mysql和sequelize的nodejs中，我使用findAll函数通过 Controller 渲染页面并包含两个关联的表。然后我渲染一个 View 页面。我浏览了列出主表中字段数据的表，并希望从另一个表中添加一些关联日期。具体来说，我将 Sales 表中的 saleId 存储在 Project 表中。当我列出 Project 的行时，我想在 Sales 中包含与项目外键中存储的 sales 相对应的名称。

我将包含下面的代码，但结果是:类型错误:E:\cjrfull\views\index.ejs:26 24| 25| <%= 项目.地址 %>、<%= 项目.csz %>

26| Sales Rep: <%= project.sales.name %>
27| Project Manager: <%= project.supervisor.name %></small> --> 28| 29|

无法读取未定义的属性“名称” 在评估(编译时评估(E:\cjrfull\node_m

From app.js:
const sequelize = require('./util/database');
const Project = require('./models/project');
const Sales = require('./models/sales');
const Supervisor = require('./models/supervisor');

...
Project.belongsTo(Sales);
Sales.hasMany(Project);
Project.belongsTo(Supervisor);
Supervisor.hasMany(Project);

from model Project.js:
const Project = sequelize.define('project', {
    id: {
        type: Sequelize.INTEGER,
        autoIncrement: true,
        allowNull: false,
        primaryKey: true
    },
    projectNo: Sequelize.STRING,
    owner1Fn: Sequelize.STRING,
    owner1Ln: Sequelize.STRING,
    owner2Fn: Sequelize.STRING,
    owner2Ln: Sequelize.STRING,
    address: {
        type: Sequelize.STRING,
        allowNull: false
    },
    csz: Sequelize.STRING,
});

from model Sales.js:
const Sales = sequelize.define('sales', {
    id: {
        type: Sequelize.INTEGER,
        autoIncrement: true,
        allowNull: false,
        primaryKey: true
    },
    name: {
        type: Sequelize.STRING,
        allowNull: false
    }
});

from Controller:
exports.getIndex = (req, res, next) => {
    Project.findAll({
            include: [{
                model: Sales
            }],
            include: [{
                model: Supervisor
            }]
        })
        .then(projects => {
            res.render('index', {
                projs: projects,
                pageTitle: 'Index',
                path: '/'
            });
        })
        .catch(err => console.log(err));
};
from View file Index.ejs:
<% for (let project of projs) { %>
                            <a href="/project/<%= project.id %>" class="list-group-item list-group-item-action active" style="background-color: darkgreen" style="border-color: darkgreen">
                                <div class="d-flex w-100 justify-content-between">
                                    <h5 class="mb-1">
                                        <%= project.owner1Fn %>
                                            <%= project.owner1Ln %><span> & </span>
                                                <%= project.owner2Fn %>
                                                    <%= project.owner2Ln %>
                                    </h5>
                                    <small>Job: <%= project.id %>  Project: <%= project.projectNo %></small>
                                </div>
                                <p class="mb-1">
                                    <%= project.address %>, <span><%= project.csz %></p>
                            <small>Sales Rep: <%= project.sales.name %> </small><br>
                            <!-- <small>Project Manager: <%= project.supervisor.name %></small> -->
                            </a>
                            <br>
                        <% } %>

Answer 1

问题在于如何包含关联的模型。您需要将它们添加到同一个数组中。

Project.findAll({
            include: [{
                model: Sales
            }, {
                model: Supervisor
            }]
        })

来自文档:

A list of associations to eagerly load using a left join. Supported is either { include: [ Model1, Model2, ...]}