Sequelize-通过另一个表包含表

Question

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

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

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

ProjectVersion的字段"version"是一个递增整数，ProjectVersionOverview的字段"text"是一个字符串。

给定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
}
}
})

我知道这不是"through"的正确用法，但作为一个例子，我认为这是有道理的。

Answer 1

您应该使用嵌套的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