时间戳

首页 > 解决方案 > Sequelize - 通过另一个表包含表

javascript - Sequelize - 通过另一个表包含表

问题描述

给定表 Project、ProjectVersion、ProjectVersionOverview 和以下关联；

Project.hasMany(ProjectVersion);
ProjectVersion.belongsTo(Project);

ProjectVersion.hasMany(ProjectVersionOverview);
ProjectVersionOverview.belongsTo(ProjectVersion);

所以基本上 Project -> 有很多 ProjectVersion -> 有很多 ProjectVersionOverview

ProjectVersion 具有字段“版本”，它是一个递增的整数，ProjectVersionOverview 具有字段“文本”，它是一个字符串。

给定projectId，我想返回以下内容；

result: {
   "projectId": 1,
   "projectVersionOverviews": [
        { "text": ... },
        { "text": ... },
   ]
}

我想要的是 ProjectVersionOverview 将根据 ProjectVersion 中的最新版本记录获取，充当更多的隐含对象而不包含在输出中。

我正在寻找的查询是这样的；

Project.findOne({
    where: { id: projectId },
    include: {
        model: ProjectVersionOverview,
        through: {
            model: ProjectVersion,
            order: [[ "version", "DESC" ]],
            limit: 1
        }
    }
})

我知道这不是“通过”的正确用法，但是作为一个例子，我认为这是有道理的。

标签： javascriptsequelize.jsassociations

解决方案

您应该使用嵌套include选项，这是一个工作示例：

index.ts：

import { sequelize } from '../../db';
import { Model, DataTypes } from 'sequelize';

class Project extends Model {}
Project.init({}, { sequelize, modelName: 'projects' });

class ProjectVersion extends Model {}
ProjectVersion.init(
  {
    version: {
      primaryKey: true,
      autoIncrement: true,
      type: DataTypes.INTEGER,
      allowNull: false,
    },
  },
  { sequelize, modelName: 'project_versions' },
);

class ProjectVersionOverview extends Model {}
ProjectVersionOverview.init({ text: DataTypes.STRING }, { sequelize, modelName: 'project_version_overviews' });

Project.hasMany(ProjectVersion);
ProjectVersion.belongsTo(Project);

ProjectVersion.hasMany(ProjectVersionOverview);
ProjectVersionOverview.belongsTo(ProjectVersion);

(async function test() {
  try {
    // create tables
    await sequelize.sync({ force: true });
    // seed
    await Project.create(
      {
        project_versions: [
          {
            project_version_overviews: [{ text: 'a' }, { text: 'b' }],
          },
          {
            project_version_overviews: [{ text: 'x' }, { text: 'y' }, { text: 'z' }],
          },
        ],
      },
      { include: [{ model: ProjectVersion, include: [ProjectVersionOverview] }] },
    );
    // test
    const result = await Project.findOne({
      where: { id: 1 },
      include: [
        {
          model: ProjectVersion,
          attributes: ['version'],
          include: [
            {
              model: ProjectVersionOverview,
              attributes: ['text'],
            },
          ],
        },
      ],
      raw: true,
    });
    console.log(result);
  } catch (error) {
    console.log(error);
  } finally {
    await sequelize.close();
  }
})();

执行结果：

Executing (default): DROP TABLE IF EXISTS "project_version_overviews" CASCADE;
Executing (default): DROP TABLE IF EXISTS "project_versions" CASCADE;
Executing (default): DROP TABLE IF EXISTS "projects" CASCADE;
Executing (default): DROP TABLE IF EXISTS "projects" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "projects" ("id"   SERIAL , PRIMARY KEY ("id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'projects' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): DROP TABLE IF EXISTS "project_versions" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "project_versions" ("version"   SERIAL , "projectId" INTEGER REFERENCES "projects" ("id") ON DELETE SET NULL ON UPDATE CASCADE, PRIMARY KEY ("version"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'project_versions' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): DROP TABLE IF EXISTS "project_version_overviews" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "project_version_overviews" ("id"   SERIAL , "text" VARCHAR(255), "projectVersionVersion" INTEGER REFERENCES "project_versions" ("version") ON DELETE SET NULL ON UPDATE CASCADE, PRIMARY KEY ("id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'project_version_overviews' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): INSERT INTO "projects" ("id") VALUES (DEFAULT) RETURNING *;
Executing (default): INSERT INTO "project_versions" ("version","projectId") VALUES (DEFAULT,$1) RETURNING *;
Executing (default): INSERT INTO "project_versions" ("version","projectId") VALUES (DEFAULT,$1) RETURNING *;
Executing (default): INSERT INTO "project_version_overviews" ("id","text","projectVersionVersion") VALUES (DEFAULT,$1,$2) RETURNING *;
Executing (default): INSERT INTO "project_version_overviews" ("id","text","projectVersionVersion") VALUES (DEFAULT,$1,$2) RETURNING *;
Executing (default): INSERT INTO "project_version_overviews" ("id","text","projectVersionVersion") VALUES (DEFAULT,$1,$2) RETURNING *;
Executing (default): INSERT INTO "project_version_overviews" ("id","text","projectVersionVersion") VALUES (DEFAULT,$1,$2) RETURNING *;
Executing (default): INSERT INTO "project_version_overviews" ("id","text","projectVersionVersion") VALUES (DEFAULT,$1,$2) RETURNING *;
Executing (default): SELECT "projects"."id", "project_versions"."version" AS "project_versions.version", "project_versions->project_version_overviews"."id" AS "project_versions.project_version_overviews.id", "project_versions->project_version_overviews"."text" AS "project_versions.project_version_overviews.text" FROM "projects" AS "projects" LEFT OUTER JOIN "project_versions" AS "project_versions" ON "projects"."id" = "project_versions"."projectId" LEFT OUTER JOIN "project_version_overviews" AS "project_versions->project_version_overviews" ON "project_versions"."version" = "project_versions->project_version_overviews"."projectVersionVersion" WHERE "projects"."id" = 1;
{ id: 1,
  'project_versions.version': 1,
  'project_versions.project_version_overviews.id': 1,
  'project_versions.project_version_overviews.text': 'a' }

检查数据库：

node-sequelize-examples=# select * from "projects";
 id
----
  1
(1 row)

node-sequelize-examples=# select * from "project_versions";
 version | projectId
---------+-----------
       1 |         1
       2 |         1
(2 rows)

node-sequelize-examples=# select * from "project_version_overviews";
 id | text | projectVersionVersion
----+------+-----------------------
  1 | a    |                     1
  2 | b    |                     1
  3 | x    |                     2
  4 | y    |                     2
  5 | z    |                     2
(5 rows)

依赖版本："sequelize": "^5.21.3",postgres:9.6

源代码：https ://github.com/mrdulin/node-sequelize-examples/tree/master/src/examples/stackoverflow/61109003

推荐阅读