如何在 sequelize 中应用联合查询-解网

问：

Project.model.ts

import { DataTypes, Model, CreationOptional, InferCreationAttributes, InferAttributes, BelongsToManyGetAssociationsMixin, BelongsToManySetAssociationsMixin, BelongsToManyAddAssociationsMixin } from 'sequelize';
import sequelize from '../../loaders/db'; // config of sequelize
import Manager from '../manager/manager.model';

const tableName = 'project';

export interface ProjectModel extends Model<InferAttributes<ProjectModel>, InferCreationAttributes<ProjectModel>>  {
  id:  CreationOptional<number>;
  name: string;
  managerId: number | null;
}

const Project = sequelize().define<ProjectModel>(
  tableName,
  {
    id: {
      type: DataTypes.UUID,
      primaryKey: true,
      defaultValue: DataTypes.UUIDV4,
    },
    name: {
      type: DataTypes.STRING(100),
      allowNull: false,
    },
    managerId: {
      type: DataTypes.INTEGER,
      allowNull: false,
      references: {
        model: Manager, // Reference Manager model
        key: 'id',
      },
      field: 'conformance_id',
    },
  },
  {
    timestamps: false,
  }
);

export default Project;

Page.model.ts

import { DataTypes, Model, CreationOptional, InferCreationAttributes, InferAttributes, BelongsToManyGetAssociationsMixin, BelongsToManySetAssociationsMixin, BelongsToManyAddAssociationsMixin } from 'sequelize';
import sequelize from '../../loaders/db'; // config of sequelize
import Project from '../project/project.model';

const tableName = 'page';

export interface PageModel extends Model<InferAttributes<PageModel>, InferCreationAttributes<PageModel>> {
  id: CreationOptional<number>;
  projectId: number; // Foreign key to link with the Project model
  name: string;
}

const Page = sequelize().define<PageModel>(
  tableName,
  {
    id: {
      type: DataTypes.UUID,
      primaryKey: true,
      defaultValue: DataTypes.UUIDV4,
    },
    projectId: {
      type: DataTypes.UUID,
      allowNull: false,
      references: {
        model: Project, // Reference Project model
        key: 'id',
      },
      field: 'project_id'
    },
    name: {
      type: DataTypes.STRING(50),
      allowNull: true,
      field: 'name'
    },
  },
  {
    timestamps: false,
  }
);

export default Page;

Manager.model.ts

import { DataTypes, Model, CreationOptional, InferCreationAttributes, InferAttributes, BelongsToManyGetAssociationsMixin, BelongsToManySetAssociationsMixin, BelongsToManyAddAssociationsMixin } from 'sequelize';
import sequelize from '../../loaders/db'; // config of sequelize

const tableName = 'manager';

export interface ManagerModel extends Model<InferAttributes<ManagerModel>, InferCreationAttributes<ManagerModel>> {
  id: CreationOptional<number>;
  level: string; // Foreign key to link with the Project model
  name: string;
}

const Manager = sequelize().define<ManagerModel>(
  tableName,
  {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true,
    },
    level: {
      type: DataTypes.STRING(10),
      allowNull: false,
    },
    name: {
      type: DataTypes.STRING(50),
      allowNull: true,
      field: 'name'
    },
  },
  {
    timestamps: false,
  }
);

export default Manager;

Project.service.ts

import { Service } from 'typedi';
import Project from './project.model';

@Service()
export default class ProjectService {
  async getProjectById(id: string) {
    try {
      const project = await Project.findOne({
        where: { id },
        // remaining part of the query to get manager and page details using any operator
      });
      if (!project) {
        throw new Error('Project not found');
      }
      return project;
    } catch (error) {
      throw new Error('Failed to fetch Project by ID');
    }
  }
}

我是这个续集的新手。现在我不确定我必须添加哪种查询 Project.findOne（）方法。我需要经理表中的经理名称以及与该项目关联的页面数组的名称。提前感谢您的帮助和时间。

节点 .js 打字稿 PostgreSQL Express 续集 .js

@Anatoly，不，我还没有定义 belongTo 和 hasMany for model。我相信它会创建另一个表来维护项目和管理以及项目和页面之间的关系。但我愿意在这里看到你的解决方案。我必须添加什么样的关系 belongsTo 和 hasmany 对于这些模型。你能帮我吗？

0赞 Anatoly 11/18/2023

先看看 sequelize.org/docs/v6/core-concepts/assocs 并尝试一下，如果您有任何问题，请随时提问。

答：

0赞 Adrian Maxwell 11/17/2023 #1

下面是一个（未经测试的）示例，用于指定关联的模型（Manager 和 Page）以及要包含在结果中的属性。因此，这应该会获取一个项目以及经理的姓名和相关页面的数组。include

import { Op } from 'sequelize';
import Project from './project.model';
import Manager from '../manager/manager.model';
import Page from '../page/page.model';

@Service()
export default class ProjectService {
  async getProjectById(id: string) {
    try {
      const project = await Project.findOne({
        where: { id },
        include: [
          {
            model: Manager,
            attributes: ['name'], // Include only the 'name' attribute of the Manager model
          },
          {
            model: Page,
            attributes: ['name'], // Include only the 'name' attribute of the Page model
          },
        ],
      });
      if (!project) {
        throw new Error('Project not found');
      }
      return project;
    } catch (error) {
      throw new Error('Failed to fetch Project by ID');
    }
  }
}

@Adrain麦克斯韦，不，这是行不通的。TBH 我已经尝试过这个解决方案，但 include 不起作用。{“level”：“error”，“message”：“无法按 ID 获取项目”，“stack”：“错误：无法按 ID 获取项目”}。如果我在查询中添加包含部分，就会出现这样的错误。没有它，它就可以完美地工作，但显然它不会提供另一个表数据。

上一个：npm 更新的 UI （Windows） [已关闭]

下一个：NodeJS 与 Objection 和 Knex - 错误：无法获取连接

如何在 sequelize 中应用联合查询

How to apply joint query in sequelize

评论

评论