How to reference parent model column from an include in Sequelize?

user_1337422 0
Viewed 484

Problem

Hey everyone. I'm new to Sequelize and am getting several rows out of the InterestModel using an array of interest ids - the interests do not all share the same categoryId. For each interest, I'm attempting to include MediaModel rows that do not share the categoryId with the interest returned.

How do I reference the categoryId property of the query's parent InterestModel from the include?

I've attempted solutions from this SO post, but I keep getting some variation of the error Unknown column 'interest.categoryId' in 'where clause'.

Query

Here is the query, with various attempts I've tried.

const interestIds: [ 2, 4, 6, ...];

const query = {
  where: { id: interestIds },
  attributes: [ 'id', 'userId', 'categoryId' ], // <-- this is categoryId
  include: [
    {
      model: UserModel,
      as: 'user',
      attributes: [
        'id',
        'avatarType',
        'avatarUrls',
        'firstName',
        'bio',
      ],
      include: [
        {
          model: InterestModel,
          as: 'interests',
          attributes: [ 'categoryId' ],
        },
        {
          model: MediaModel,
          // Attempts:
          // where: database.literal('media.categoryId != interest.categoryId'), // ?
          // where: { categoryId: { [Op.not]: { [Op.col]: 'InterestModel.categoryId' } },
          // where: { categoryId: { [Op.not]: { [Op.col]: 'Interest.categoryId' } },
          // where: { categoryId: { [Op.not]: { [Op.col]: 'interest.categoryId' } },
          // where: { categoryId: { [Op.not]: { [Op.col]: '`interest`.`categoryId`' } },
          attributes: [ 'userId', 'urls' ],
          order: database.literal('rand()'),
          limit: 6,
        },
      ],
    },
  ],
};

return InterestModel.findAll(query).then(interests => { ...etc });

Logging

And here is the relevant logging output from Sequelize:

Executing (default): SELECT `interest`.`id`, `interest`.`userId`, `interest`.`categoryId`, `interest`.`familiarity`, `interest`.`eloScore`, `user`.`id` AS `user.id`, `user`.`avatarType` AS `user.avatarType`, `user`.`avatarUrls` AS `user.avatarUrls`, `user`.`firstName` AS `user.firstName`, `user`.`bio` AS `user.bio`, `user->interests`.`id` AS `user.interests.id`, `user->interests`.`categoryId` AS `user.interests.categoryId`, `category`.`id` AS `category.id`, `category`.`name` AS `category.name`, `category->subcategories`.`id` AS `category.subcategories.id`, `category->subcategories`.`categoryId` AS `category.subcategories.categoryId`, `category->subcategories`.`name` AS `category.subcategories.name`, `category->prompts`.`id` AS `category.prompts.id`, `category->prompts`.`question` AS `category.prompts.question`, `category->prompts`.`categoryId` AS `category.prompts.categoryId`, `answers`.`id` AS `answers.id`, `answers`.`text` AS `answers.text`, `answers`.`promptId` AS `answers.promptId` FROM `interests` AS `interest` LEFT OUTER JOIN `users` AS `user` ON `interest`.`userId` = `user`.`id` AND (`user`.`deletedAt` > '2020-05-29 05:47:28' OR `user`.`deletedAt` IS NULL) LEFT OUTER JOIN `interests` AS `user->interests` ON `user`.`id` = `user->interests`.`userId` AND (`user->interests`.`deletedAt` > '2020-05-29 05:47:28' OR `user->interests`.`deletedAt` IS NULL) LEFT OUTER JOIN `categories` AS `category` ON `interest`.`categoryId` = `category`.`id` AND (`category`.`deletedAt` > '2020-05-29 05:47:28' OR `category`.`deletedAt` IS NULL) LEFT OUTER JOIN `subcategories` AS `category->subcategories` ON `category`.`id` = `category->subcategories`.`categoryId` AND (`category->subcategories`.`deletedAt` > '2020-05-29 05:47:28' OR `category->subcategories`.`deletedAt` IS NULL) LEFT OUTER JOIN `prompts` AS `category->prompts` ON `category`.`id` = `category->prompts`.`categoryId` AND (`category->prompts`.`deletedAt` > '2020-05-29 05:47:28' OR `category->prompts`.`deletedAt` IS NULL) LEFT OUTER JOIN `answers` AS `answers` ON `interest`.`id` = `answers`.`interestId` WHERE ((`interest`.`deletedAt` > '2020-05-29 05:47:28' OR `interest`.`deletedAt` IS NULL) AND `interest`.`id` IN (60, 46, 47, 48, 51, 62));

Executing (default): SELECT `media`.* FROM ((SELECT `id`, `userId`, `urls` FROM `media` AS `media` WHERE ((`media`.`deletedAt` > '2020-05-29 05:47:29' OR `media`.`deletedAt` IS NULL) AND (((`media`.`deletedAt` > '2020-05-29 05:47:28' OR `media`.`deletedAt` IS NULL) AND `media`.`categoryId` = `interest`.`categoryId`))) AND `media`.`userId` = 53 ORDER BY rand() LIMIT 6) UNION ALL (SELECT `id`, `userId`, `urls` FROM `media` AS `media` WHERE ((`media`.`deletedAt` > '2020-05-29 05:47:29' OR `media`.`deletedAt` IS NULL) AND (((`media`.`deletedAt` > '2020-05-29 05:47:28' OR `media`.`deletedAt` IS NULL) AND `media`.`categoryId` = `interest`.`categoryId`))) AND `media`.`userId` = 54 ORDER BY rand() LIMIT 6) UNION ALL (SELECT `id`, `userId`, `urls` FROM `media` AS `media` WHERE ((`media`.`deletedAt` > '2020-05-29 05:47:29' OR `media`.`deletedAt` IS NULL) AND (((`media`.`deletedAt` > '2020-05-29 05:47:28' OR `media`.`deletedAt` IS NULL) AND `media`.`categoryId` = `interest`.`categoryId`))) AND `media`.`userId` = 55 ORDER BY rand() LIMIT 6) UNION ALL (SELECT `id`, `userId`, `urls` FROM `media` AS `media` WHERE ((`media`.`deletedAt` > '2020-05-29 05:47:29' OR `media`.`deletedAt` IS NULL) AND (((`media`.`deletedAt` > '2020-05-29 05:47:28' OR `media`.`deletedAt` IS NULL) AND `media`.`categoryId` = `interest`.`categoryId`))) AND `media`.`userId` = 56 ORDER BY rand() LIMIT 6) UNION ALL (SELECT `id`, `userId`, `urls` FROM `media` AS `media` WHERE ((`media`.`deletedAt` > '2020-05-29 05:47:29' OR `media`.`deletedAt` IS NULL) AND (((`media`.`deletedAt` > '2020-05-29 05:47:28' OR `media`.`deletedAt` IS NULL) AND `media`.`categoryId` = `interest`.`categoryId`))) AND `media`.`userId` = 56 ORDER BY rand() LIMIT 6) UNION ALL (SELECT `id`, `userId`, `urls` FROM `media` AS `media` WHERE ((`media`.`deletedAt` > '2020-05-29 05:47:29' OR `media`.`deletedAt` IS NULL) AND (((`media`.`deletedAt` > '2020-05-29 05:47:28' OR `media`.`deletedAt` IS NULL) AND `media`.`categoryId` = `interest`.`categoryId`))) AND `media`.`userId` = 55 ORDER BY rand() LIMIT 6)) AS `media`;

Error

And here is the error message when trying to use [Op.col]:

DatabaseError [SequelizeDatabaseError]: Unknown column 'interest.categoryId' in 'where clause'
    at Query.formatError (/Users/sgarza62/ditto-app/api/node_modules/sequelize/lib/dialects/mysql/query.js:247:16)
    at Query.handler [as onResult] (/Users/sgarza62/ditto-app/api/node_modules/sequelize/lib/dialects/mysql/query.js:68:23)
    at Query.Command.execute (/Users/sgarza62/ditto-app/api/node_modules/mysql2/lib/commands/command.js:30:12)
    at Connection.handlePacket (/Users/sgarza62/ditto-app/api/node_modules/mysql2/lib/connection.js:513:28)
    at PacketParser.onPacket (/Users/sgarza62/ditto-app/api/node_modules/mysql2/lib/connection.js:81:16)
    at PacketParser.executeStart (/Users/sgarza62/ditto-app/api/node_modules/mysql2/lib/packet_parser.js:76:14)
    at Socket.<anonymous> (/Users/sgarza62/ditto-app/api/node_modules/mysql2/lib/connection.js:89:29)
    at Socket.emit (events.js:310:20)
    at Socket.EventEmitter.emit (domain.js:482:12)
    at addChunk (_stream_readable.js:286:12)
    at readableAddChunk (_stream_readable.js:268:9)
    at Socket.Readable.push (_stream_readable.js:209:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:186:23) {
  name: 'SequelizeDatabaseError',
  parent: Error: Unknown column 'interest.categoryId' in 'where clause'
      at Packet.asError (/Users/sgarza62/ditto-app/api/node_modules/mysql2/lib/packets/packet.js:714:13)
      at Query.Command.execute (/Users/sgarza62/ditto-app/api/node_modules/mysql2/lib/commands/command.js:28:22)
      at Connection.handlePacket (/Users/sgarza62/ditto-app/api/node_modules/mysql2/lib/connection.js:513:28)
      at PacketParser.onPacket (/Users/sgarza62/ditto-app/api/node_modules/mysql2/lib/connection.js:81:16)
      at PacketParser.executeStart (/Users/sgarza62/ditto-app/api/node_modules/mysql2/lib/packet_parser.js:76:14)
      at Socket.<anonymous> (/Users/sgarza62/ditto-app/api/node_modules/mysql2/lib/connection.js:89:29)
      at Socket.emit (events.js:310:20)
      at Socket.EventEmitter.emit (domain.js:482:12)
      at addChunk (_stream_readable.js:286:12)
      at readableAddChunk (_stream_readable.js:268:9)
      at Socket.Readable.push (_stream_readable.js:209:10)
      at TCP.onStreamRead (internal/stream_base_commons.js:186:23) {
    code: 'ER_BAD_FIELD_ERROR',
    errno: 1054,
    sqlState: '42S22',
    sqlMessage: "Unknown column 'interest.categoryId' in 'where clause'",
    sql: "SELECT `media`.* FROM ((SELECT `id`, `userId`, `urls` FROM `media` AS `media` WHERE ((`media`.`deletedAt` > '2020-05-29 05:47:29' OR `media`.`deletedAt` IS NULL) AND (((`media`.`deletedAt` > '2020-05-29 05:47:28' OR `media`.`deletedAt` IS NULL) AND `media`.`categoryId` = `interest`.`categoryId`))) AND `media`.`userId` = 53 ORDER BY rand() LIMIT 6) UNION ALL (SELECT `id`, `userId`, `urls` FROM `media` AS `media` WHERE ((`media`.`deletedAt` > '2020-05-29 05:47:29' OR `media`.`deletedAt` IS NULL) AND (((`media`.`deletedAt` > '2020-05-29 05:47:28' OR `media`.`deletedAt` IS NULL) AND `media`.`categoryId` = `interest`.`categoryId`))) AND `media`.`userId` = 54 ORDER BY rand() LIMIT 6) UNION ALL (SELECT `id`, `userId`, `urls` FROM `media` AS `media` WHERE ((`media`.`deletedAt` > '2020-05-29 05:47:29' OR `media`.`deletedAt` IS NULL) AND (((`media`.`deletedAt` > '2020-05-29 05:47:28' OR `media`.`deletedAt` IS NULL) AND `media`.`categoryId` = `interest`.`categoryId`))) AND `media`.`userId` = 55 ORDER BY rand() LIMIT 6) UNION ALL (SELECT `id`, `userId`, `urls` FROM `media` AS `media` WHERE ((`media`.`deletedAt` > '2020-05-29 05:47:29' OR `media`.`deletedAt` IS NULL) AND (((`media`.`deletedAt` > '2020-05-29 05:47:28' OR `media`.`deletedAt` IS NULL) AND `media`.`categoryId` = `interest`.`categoryId`))) AND `media`.`userId` = 56 ORDER BY rand() LIMIT 6) UNION ALL (SELECT `id`, `userId`, `urls` FROM `media` AS `media` WHERE ((`media`.`deletedAt` > '2020-05-29 05:47:29' OR `media`.`deletedAt` IS NULL) AND (((`media`.`deletedAt` > '2020-05-29 05:47:28' OR `media`.`deletedAt` IS NULL) AND `media`.`categoryId` = `interest`.`categoryId`))) AND `media`.`userId` = 56 ORDER BY rand() LIMIT 6) UNION ALL (SELECT `id`, `userId`, `urls` FROM `media` AS `media` WHERE ((`media`.`deletedAt` > '2020-05-29 05:47:29' OR `media`.`deletedAt` IS NULL) AND (((`media`.`deletedAt` > '2020-05-29 05:47:28' OR `media`.`deletedAt` IS NULL) AND `media`.`categoryId` = `interest`.`categoryId`))) AND `media`.`userId` = 55 ORDER BY rand() LIMIT 6)) AS `media`;"

Edit requested in the comments, showing model and association:

const InterestModel = database.define('interest', {
  id: {
    type: Sequelize.INTEGER(10).UNSIGNED,
    primaryKey: true,
    autoIncrement: true,
  },
  userId: {
    type: Sequelize.INTEGER(10).UNSIGNED,
    allowNull: false,
  },
  categoryId: {
    type: Sequelize.INTEGER(10).UNSIGNED,
    allowNull: false,
  },
});

InterestModel.belongsTo(UserModel);

InterestModel.belongsTo(CategoryModel);

CategoryModel.hasMany(
  MediaModel, { as: 'media'}
);

CategoryModel.hasMany(
  InterestModel, { as: 'interests'}
);
sequelize.js node.js mysql
0 Answers
Related