Sequelize find by association through manually-defined join table

Question

I know that there is a simpler case described here:

Unfortunately, my case is a bit more complex than that. I have a User model which belongsToMany Departments (which in turn belongsToMany Users), but does so through userDepartment, a manually defined join table. My goal is to get all the users belonging to a given department. First let's look at models/user.js:

var user = sequelize.define("user", {
    id: {
        type: DataTypes.INTEGER,
        field: 'emplId',
        primaryKey: true,
        autoIncrement: false
    },
    firstname: {
        type: DataTypes.STRING,
        field: 'firstname_preferred',
        defaultValue: '',
        allowNull: false
    }
    ...
    ...
    ...

    associate: function(models) {
            user.belongsToMany(models.department, {
                foreignKey: "emplId",
                through: 'userDepartment'
                });
            })
    }
    ...
    return user;

Now, a look at models/department.js:

var department = sequelize.define("department", {
    id: {
        type: DataTypes.INTEGER,
        field: 'departmentId',
        primaryKey: true,
        autoIncrement: true
    },
    ...
    classMethods: {
        associate: function(models) {

            department.belongsToMany(models.user, {
                foreignKey: "departmentId",
                through: 'userDepartment',
                onDelete: 'cascade'
            });
        }

    ...
return department;

And finally at models/userDepartment.js:

var userDepartment = sequelize.define("userDepartment", {
    title: {
        type: DataTypes.STRING,
        field: 'title',
        allowNull: false,
        defaultValue: ''
    }
}, {
    tableName: 'user_departments'
});

return userDepartment;

So far so good. However, this query:

models.user.findAll({
    where: {'departments.id': req.params.id},
    include: [{model: models.department, as: models.department.tableName}]

})

Fails with the following error:

SequelizeDatabaseError: ER_BAD_FIELD_ERROR: Unknown column 'user.departments.id' in 'where clause'

Attempting to include userDepartment model results in:

Error: userDepartment (user_departments) is not associated to user!

In short: I have two Sequelize Models with a M:M relationship. They are associated through a manually defined join table (which adds a job title to each unique relationship, i.e., User A is a "Manager" in Department B). Attempting to find Users by Department fails with a bad table name error.

sequelize version "^2.0.5"

Answer 1

You can also use the auto-generated instance.getOthers() method if you have a class instance

This does potentially mean one extra query. But if the instance is already at hand, this is the most convenient syntax.

Supposing a "user likes post with given score" situation, we can get all the posts that a user likes with:

const user0 = await User.create({name: 'user0'})
const user0Likes = await user0.getPosts({order: [['body', 'ASC']]})
assert(user0Likes[0].body === 'post0');
assert(user0Likes[0].UserLikesPost.score === 1);
assert(user0Likes.length === 1);

Full runnable example:

main.js

const assert = require('assert')
const { DataTypes, Op, Sequelize } = require('sequelize')
const common = require('./common')
const sequelize = common.sequelize(__filename, process.argv[2], { define: { timestamps: false } })
;(async () => {

// Create the tables.
const User = sequelize.define('User', {
  name: { type: DataTypes.STRING },
});
const Post = sequelize.define('Post', {
  body: { type: DataTypes.STRING },
});
const UserLikesPost = sequelize.define('UserLikesPost', {
  UserId: {
    type: DataTypes.INTEGER,
    references: {
      model: User,
      key: 'id'
    }
  },
  PostId: {
    type: DataTypes.INTEGER,
    references: {
      model: Post,
      key: 'id'
    }
  },
  score: {
    type: DataTypes.INTEGER,
  },
});
User.belongsToMany(Post, {through: UserLikesPost});
Post.belongsToMany(User, {through: UserLikesPost});
await sequelize.sync({force: true});

// Create some users and likes.

const user0 = await User.create({name: 'user0'})
const user1 = await User.create({name: 'user1'})
const user2 = await User.create({name: 'user2'})

const post0 = await Post.create({body: 'post0'});
const post1 = await Post.create({body: 'post1'});
const post2 = await Post.create({body: 'post2'});

// Autogenerated add* methods

// Make some useres like some posts.
await user0.addPost(post0, {through: {score: 1}})
await user1.addPost(post1, {through: {score: 2}})
await user1.addPost(post2, {through: {score: 3}})

// Find what user0 likes.
const user0Likes = await user0.getPosts({order: [['body', 'ASC']]})
assert(user0Likes[0].body === 'post0');
assert(user0Likes[0].UserLikesPost.score === 1);
assert(user0Likes.length === 1);

// Find what user1 likes.
const user1Likes = await user1.getPosts({order: [['body', 'ASC']]})
assert(user1Likes[0].body === 'post1');
assert(user1Likes[0].UserLikesPost.score === 2);
assert(user1Likes[1].body === 'post2');
assert(user1Likes[1].UserLikesPost.score === 3);
assert(user1Likes.length === 2);

// Where on the custom through table column.
// Find posts that user1 likes which have score greater than 2.
// https://stackoverflow.com/questions/38857156/how-to-query-many-to-many-relationship-sequelize
{
  const rows = await Post.findAll({
    include: [
      {
        model: User,
        where: {id: user1.id},
        through: {
          where: {score: { [Op.gt]: 2 }},
        },
      },
    ],
  })
  assert.strictEqual(rows[0].body, 'post2');
  // TODO how to get the score here as well?
  //assert.strictEqual(rows[0].UserLikesPost.score, 3);
  assert.strictEqual(rows.length, 1);
}

})().finally(() => { return sequelize.close() });

common.js

const path = require('path');

const { Sequelize } = require('sequelize');

function sequelize(filename, dialect, opts) {
  if (dialect === undefined) {
    dialect = 'l'
  }
  if (dialect === 'l') {
    return new Sequelize(Object.assign({
      dialect: 'sqlite',
      storage: path.parse(filename).name + '.sqlite'
    }, opts));
  } else if (dialect === 'p') {
    return new Sequelize('tmp', undefined, undefined, Object.assign({
      dialect: 'postgres',
      host: '/var/run/postgresql',
    }, opts));
  } else {
    throw new Error('Unknown dialect')
  }
}
exports.sequelize = sequelize

package.json

{
  "name": "tmp",
  "private": true,
  "version": "1.0.0",
  "dependencies": {
    "pg": "8.5.1",
    "pg-hstore": "2.3.3",
    "sequelize": "6.5.1",
    "sqlite3": "5.0.2"
  }
}

tested on PostgreSQL 13.4, Ubuntu 21.04.

Answer 2

For anyone still looking for an answer for this, I found one here on Github.

You simply do this:

where: {
   '$Table.column$' : value
 }

Answer 3

I had a similair problem, but in my case I couldn't switch the tables.

I had to make use of the: sequelize.literal function.

In your case it would look like the following:

models.user.findAll({
    where: sequelize.literal("departments.id = " + req.params.id),
    include: [{model: models.department, as: models.department.tableName}]
})

I'm not fond of it, but it works.

Answer 4

Took a couple of hours, but I found my solution:

models.department.find({
    where: {id:req.params.id},
    include: [models.user]

The problem is that Sequelize won't let you "go out of scope" because it begins each where clause with model_name. So, for example, the where clause was trying to compare user.departments.id when the departments table is only joined as departments.id. Since we're querying on a value of the department (the ID), it makes the most since to query for a single department and return their associated users.