sequelize: How to use `belongsToMany`

Question

I have the following code:

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();

My expectation was that the code should print 1, as the track has one problem. However, it prints 0. How do I achieve the desired behavior with my models?

Answer 1

It should work. You have user, track and problem models. You use hasMany association which means they are one-to-many associations. user has many track, track has many problem. If so, you don't need to use belongsToMany associations. That's many-to-many associations.

Here is a working example:

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();

You may notice that I use belongsTo associations, the reason is if you want to get the track by problem, which means if you want your problem model has getTrack method. You need to define this association.

Below is the debug message for SQL query and the printed result

☁  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

The result of await tr.countProblems() is 1