Skip to content

Set an alias for the junction table in a many to many relationship in sequelize

Apologizes if the title is confusing, I’m unsure what terminology to use here.

Basically, I have three (Four, technically, but the fourth is nearly identical to user.model.js) models:

user.model.js

'use strict';
const {Model} = require('sequelize');

module.exports = (sequelize, DataTypes) => {
    class User extends Model {
        static associate(models) {
            User.belongsToMany(models.Item, {
                through: 'UserItems',
                as: 'items',
                foreignKey: 'userId',
                otherKey: 'itemId'
            });
        }
    }

    User.init({
        name: {
            type: DataTypes.STRING,
            allowNull: false,
            unique: true
        },
        email: {
            type: DataTypes.STRING,
            allowNull: false,
            unique: true
        },
        password: {
            type: DataTypes.STRING,
            allowNull: false
        }
    }, {
        sequelize,
        modelName: 'User',
        paranoid: true,
        associations: true
    });
    return User;
};

item.model.js

'use strict';
const {
    Model
} = require('sequelize');
module.exports = (sequelize, DataTypes) => {
    class Item extends Model {
        /**
         * Helper method for defining associations.
         * This method is not a part of Sequelize lifecycle.
         * The `models/index` file will call this method automatically.
         */
        static associate(models) {
            Item.belongsToMany(models.Guest, {
                through: {
                    model: 'GuestItems',
                    unique: false
                },
                constraints: false,
                as: 'guests',
                foreignKey: 'itemId',
                otherKey: 'guestId'
            });
            Item.belongsToMany(models.User, {
                through: {
                    model: 'UserItems',
                    unique: false
                },
                constraints: false,
                as: 'users',
                foreignKey: 'itemId',
                otherKey: 'userId'
            });
        }
    }

    Item.init({
        dexId: {
            allowNull: true,
            type: DataTypes.INTEGER,
            defaultValue: null
        },
        name: {
            type: DataTypes.STRING,
            unique: true,
            allowNull: false
        },
        description: DataTypes.STRING,
        filename: DataTypes.STRING,
        firstSeen: {
            type: DataTypes.DATE,
            allowNull: true,
            defaultValue: null
        },
        firstAcquired: {
            type: DataTypes.DATE,
            allowNull: true,
            defaultValue: null
        },
    }, {
        sequelize,
        modelName: 'Item',
        paranoid: true
    });
    return Item;
};

useritems.model.js

'use strict';
const {
  Model
} = require('sequelize');
module.exports = (sequelize, DataTypes) => {
  class UserItems extends Model {
    /**
     * Helper method for defining associations.
     * This method is not a part of Sequelize lifecycle.
     * The `models/index` file will call this method automatically.
     */
    static associate(models) {
      // define association here
    }
  };
  UserItems.init({
    userId: DataTypes.INTEGER,
    itemId: DataTypes.INTEGER,
    seen: DataTypes.BOOLEAN,
    owned: DataTypes.BOOLEAN
  }, {
    sequelize,
    modelName: 'UserItems',
  });
  return UserItems;
};

In my controller, I have

User.findByPk(id,
            {
                include: { model: db.Item, as: 'items', through: { attributes: ['seen']} },
                order: [[sequelize.literal('"items"."dexId"'), 'ASC']],
            })
            .then(data => {
                console.log(data.items);
                res.status(200).json({ items: data.items});
            })
            .catch(err => {
                console.log(err);
                res.status(204).json({ items: []});
            });

Now this works perfectly fine, except for the fact that the ‘seen’ and ‘owned’ columns are added to an additional object called UserItems.

Example of the returned item:

Item {
    dataValues: {
      id: 65,
      dexId: null,
      name: 'Fiona29',
      description: 'Quasi et sint.',
      filename: 'http://placeimg.com/640/480/animals',
      firstSeen: 2021-08-25T18:25:52.125Z,
      firstAcquired: 2021-08-26T02:36:05.250Z,
      createdAt: 2021-08-25T02:29:07.511Z,
      updatedAt: 2021-08-26T02:36:05.249Z,
      deletedAt: null,
      UserItems: [UserItems]
    },
    _previousDataValues: {
      id: 65,
      dexId: null,
      name: 'Fiona29',
      description: 'Quasi et sint.',
      filename: 'http://placeimg.com/640/480/animals',
      firstSeen: 2021-08-25T18:25:52.125Z,
      firstAcquired: 2021-08-26T02:36:05.250Z,
      createdAt: 2021-08-25T02:29:07.511Z,
      updatedAt: 2021-08-26T02:36:05.249Z,
      deletedAt: null,
      UserItems: [UserItems]
    },
    _changed: Set(0) {},
    _options: {
      isNewRecord: false,
      _schema: null,
      _schemaDelimiter: '',
      include: [Array],
      includeNames: [Array],
      includeMap: [Object],
      includeValidated: true,
      raw: true,
      attributes: undefined
    },
    isNewRecord: false,
    UserItems: UserItems {
      dataValues: [Object],
      _previousDataValues: [Object],
      _changed: Set(0) {},
      _options: [Object],
      isNewRecord: false
    }
  },

This is less than ideal for what I’m using the data for – basically, there’s also a Guest table with the equivalent many-to-many relation with the Item table, and the join table for it is called GuestItems. So when I search for a guest – the above result spits out the same data, but with GuestItems instead of UserItems.

What I want to happen, is have UserItems/GuestItems be renamed to Items. Or ideally, just have the ‘seen’ and ‘owned’ columns from the UserItems/GuestItems table be added to the rest of the results, without being its own ‘UserItems/GuestItems’ attribute.

Is this possible to accomplish with Sequelize? I’d really rather not have to add another step of renaming the object in the return.

Answer

To just rename the UserItems table you can use the “as” option in the “through” option:

User.findByPk(id, 
    {
        include: { model: db.Item, as: 'items', through: { as: "Items", attributes: ['seen', 'owned']} },
        order: [[sequelize.literal('"items"."dexId"'), 'ASC']],
    })
    .then(data => {
        console.log(data.items);
        res.status(200).json({ items: data.items});
    })
    .catch(err => {
        console.log(err);
        res.status(204).json({ items: []});
    });

This should return Item as:

Item {
    dataValues: {
      id: 65,
      dexId: null,
      name: 'Fiona29',
      description: 'Quasi et sint.',
      filename: 'http://placeimg.com/640/480/animals',
      firstSeen: 2021-08-25T18:25:52.125Z,
      firstAcquired: 2021-08-26T02:36:05.250Z,
      createdAt: 2021-08-25T02:29:07.511Z,
      updatedAt: 2021-08-26T02:36:05.249Z,
      deletedAt: null,
      Items: {
        seen: false,
        owned: false
      }
    }

As for saving them directly in ‘Item’ not sure if that can be done with sequelize finders, you probably need to write your own raw SQL query to join the ‘seen’ and ‘owned’ columns on ‘UserItems’ with ‘Item’.