javascript - Sequelize 中的复杂 queeering（包括、排序、限制）

Question

我有 3 个模型

module.exports = (sequelize, DataTypes) => {
  class User extends Model {
    static associate({ Chat, Message }) {
      this.belongsToMany(Chat, {
        through: 'UsersChats',
        foreignKey: 'userId',
        as: 'chats'
      });
      this.hasMany(Message, { as: 'messages', foreignKey: 'userId' });
    }
  }
  User.init(
    {
      login: {
        type: DataTypes.STRING,
        allowNull: false,
        unique: true
      },
      password: {
        type: DataTypes.STRING,
        allowNull: false
      }
    },
    {
      sequelize,
      modelName: 'User'
    }
  );
  return User;
};

module.exports = (sequelize, DataTypes) => {
  class Chat extends Model {
    static associate({ Message, User }) {
      this.hasMany(Message, { as: 'messages', foreignKey: 'chatId' });
      this.belongsToMany(User, {
        through: 'UsersChats',
        foreignKey: 'chatId',
        as: 'users'
      });
    }
  }
  Chat.init(
    {
      name: DataTypes.STRING
    },
    {
      sequelize,
      modelName: 'Chat'
    }
  );
  return Chat;
};

module.exports = (sequelize, DataTypes) => {
  class Message extends Model {
    static associate({ Chat, User }) {
      this.belongsTo(Chat, { foreignKey: 'chatId', as: 'chat' });
      this.belongsTo(User, { foreignKey: 'userId', as: 'user' });
    }
  }
  Message.init(
    {
      userId: DataTypes.UUID,
      text: DataTypes.TEXT,
      chatId: DataTypes.UUID
    },
    {
      sequelize,
      modelName: 'Message'
    }
  );
  return Message;
};

我正在尝试构建这样的查询来获取聊天列表，按创建排序并受最后一条消息的限制

select C.name,
       C.id,
       M.text,
       M."userId"
from "Chats" as C
         left join lateral (
    select text,
           "userId",
           "createdAt"
    from "Messages" M
    where M."chatId" = C.id
    order by "createdAt" DESC
    limit 1
    ) M on true
order by M."createdAt" DESC;

续集查询

Chat.findAll({
  include: [
    {
      model: User,
      as: 'users',
      through: {
        where: {
          id: userId
        }
      }
    },
    {
      model: Message,
      as: 'messages',
      limit: 1,
      order: [['createdAt', 'DESC']]
    }
  ],
  order: [['messages', 'createdAt', 'DESC']]
});

但是当执行查询时，我得到了一个异常

选择“聊天”。“id”，“聊天”。“名称”，“聊天”。“createdAt”，“聊天”。“updatedAt”，“用户”。“id”为“users.id”，“用户” ."login" AS "users.login", "users"."password" AS "users.password", "users"."createdAt" AS "users.createdAt", "users"."updatedAt" AS "users. updatedAt", "users->UsersChats"."createdAt" AS "users.UsersChats.createdAt", "users->UsersChats"."updatedAt" AS "users.UsersChats.updatedAt", "users->UsersChats"."chatId " AS "users.UsersChats.chatId", "users->UsersChats"."userId" AS "users.UsersChat.userId" FROM "Chats" AS "Chat" 左外连接 ( "UsersChats" AS "users->UsersChats" INNER JOIN "Users" AS "users" ON "users"."id" = "users->UsersChats"." userId" AND "users->UsersChats"."id" = 'a529cfbd-4104-4ed8-b135-443de9d3b41a') ON "Chat"."id" = "users->UsersChats"."chatId" ORDER BY "messages" ."createdAt" DESC; DatabaseError [SequelizeDatabaseError]: 缺少表 "messages" 的 FROM 子句条目UsersChats"."userId" AND "users->UsersChats"."id" = 'a529cfbd-4104-4ed8-b135-443de9d3b41a') ON "Chat"."id" = "users->UsersChats"."chatId" ORDER BY "messages"."createdAt" DESC; DatabaseError [SequelizeDatabaseError]: 缺少表 "messages" 的 FROM 子句条目UsersChats"."userId" AND "users->UsersChats"."id" = 'a529cfbd-4104-4ed8-b135-443de9d3b41a') ON "Chat"."id" = "users->UsersChats"."chatId" ORDER BY "messages"."createdAt" DESC; DatabaseError [SequelizeDatabaseError]: 缺少表 "messages" 的 FROM 子句条目

我知道这个问题order: [['messages', 'createdAt', 'DESC']]，但我需要按最后一条消息排序。我能做什么，有什么想法吗？

标签： javascriptnode.jssequelize.jspsql