node.js - Sequelize : How to use `belongsToMany`

Question

我有以下代码:

const Sequelize = require('sequelize');

const sequelize = new Sequelize({
    dialect: 'sqlite',
    storage: 'foo/database.sqlite'
});


class User extends Sequelize.Model {
}


class Problem extends Sequelize.Model {
}

class Track extends Sequelize.Model {

}

Problem.init({
        title: {type: Sequelize.STRING, primaryKey: true},
        description: Sequelize.STRING,
        votes: {type: Sequelize.INTEGER, defaultValue: 0}
    },
    {sequelize, modelName: "problem"});

Track.init({
    name: {type: Sequelize.STRING, primaryKey: true},
}, {
    sequelize,
    modelName: 'track'
});


User.init({
    name: Sequelize.STRING,
}, {
    sequelize,
    modelName: 'user'
});


User.hasMany(Track);
Track.belongsToMany(User, {through: "UserTrack"});

Track.hasMany(Problem);
Problem.belongsToMany(Track, {through: "ProblemTrack"});


async function foo() {


    await Track.sync({force: true});
    await Problem.sync({force: true});
    await User.sync({force: true});


    const u = await User.create({name: "foo"});
    const track = await Track.create({name: "track_1"});
    const problem = await Problem.create({name: "prob_1"});
    await track.addProblems([problem]);
    await u.addTracks([track]);
    const tr = await Track.findByPk("track_1");
    const probs = await tr.getProblems();
    console.log(await tr.countProblems());

}

foo();

我的期望是代码应该打印 1 ，因为 track 有一个 problem 。但是，它会打印 0 。如何使用我的模型实现所需的行为？

Answer 1

它应该工作。您有 user 、 track 和 problem 模型。您使用 hasMany 关联，这意味着它们是一对多关联。 user 有很多 track ， track 有很多 problem 。如果是这样，则不需要使用 belongsToMany 关联。这是多对多的关联。

这是一个工作示例:
models.ts :

import Sequelize, { BelongsToGetAssociationMixin } from 'sequelize';
import { sequelize } from '../../db';

class User extends Sequelize.Model {}

class Problem extends Sequelize.Model {
  public getTrack!: BelongsToGetAssociationMixin<Track>;
}

class Track extends Sequelize.Model {}

Problem.init(
  {
    title: { type: Sequelize.STRING, primaryKey: true },
    description: Sequelize.STRING,
    votes: { type: Sequelize.INTEGER, defaultValue: 0 },
  },
  { sequelize, modelName: 'problem' },
);

Track.init(
  {
    name: { type: Sequelize.STRING, primaryKey: true },
  },
  {
    sequelize,
    modelName: 'track',
  },
);

User.init(
  {
    name: Sequelize.STRING,
  },
  {
    sequelize,
    modelName: 'user',
  },
);

User.hasMany(Track);

Track.hasMany(Problem);
Problem.belongsTo(Track);

async function foo() {
  await sequelize.sync({ force: true });

  const u = await User.create({ name: 'foo' });
  const track = await Track.create({ name: 'track_1' });
  const problem = await Problem.create({ title: 'prob_1' });
  await track.addProblems([problem]);
  await u.addTracks([track]);
  const tr = await Track.findByPk('track_1');
  const probs = await tr.getProblems();
  console.log(await tr.countProblems());

  // const prob: Problem = probs[0];
  // const t = await prob.getTrack();
  // console.log('t:', t);

  await sequelize.close();
}

foo();

您可能注意到我使用了 belongsTo 关联，原因是如果您想通过 track 获取 problem ，这意味着如果您希望您的 problem 模型具有 getTrack 方法。您需要定义此关联。

下面是 SQL 查询的调试信息和打印的结果

☁  node-sequelize-examples [master] ⚡  npx ts-node /Users/ldu020/workspace/github.com/mrdulin/node-sequelize-examples/src/examples/stackoverflow/60142404/models.ts
{ POSTGRES_HOST: '127.0.0.1',
  POSTGRES_PORT: '5430',
  POSTGRES_PASSWORD: 'testpass',
  POSTGRES_USER: 'testuser',
  POSTGRES_DB: 'node-sequelize-examples' }
Executing (default): DROP TABLE IF EXISTS "problem" CASCADE;
Executing (default): DROP TABLE IF EXISTS "track" CASCADE;
Executing (default): DROP TABLE IF EXISTS "user" CASCADE;
Executing (default): DROP TABLE IF EXISTS "user" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "user" ("id"   SERIAL , "name" VARCHAR(255), 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 = 'user' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): DROP TABLE IF EXISTS "track" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "track" ("name" VARCHAR(255) , "userId" INTEGER REFERENCES "user" ("id") ON DELETE SET NULL ON UPDATE CASCADE, PRIMARY KEY ("name"));
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 = 'track' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): DROP TABLE IF EXISTS "problem" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "problem" ("title" VARCHAR(255) , "description" VARCHAR(255), "votes" INTEGER DEFAULT 0, "trackName" VARCHAR(255) REFERENCES "track" ("name") ON DELETE SET NULL ON UPDATE CASCADE, PRIMARY KEY ("title"));
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 = 'problem' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): INSERT INTO "user" ("id","name") VALUES (DEFAULT,$1) RETURNING *;
Executing (default): INSERT INTO "track" ("name") VALUES ($1) RETURNING *;
Executing (default): INSERT INTO "problem" ("title","votes") VALUES ($1,$2) RETURNING *;
Executing (default): UPDATE "problem" SET "trackName"=$1 WHERE "title" IN ('prob_1')
Executing (default): UPDATE "track" SET "userId"=$1 WHERE "name" IN ('track_1')
Executing (default): SELECT "name", "userId" FROM "track" AS "track" WHERE "track"."name" = 'track_1';
Executing (default): SELECT "title", "description", "votes", "trackName" FROM "problem" AS "problem" WHERE "problem"."trackName" = 'track_1';
Executing (default): SELECT COUNT("problem"."title") AS "count" FROM "problem" AS "problem" WHERE "problem"."trackName" = 'track_1';
1

await tr.countProblems() 的结果是 1