node.js - Sequelize-如何通过关联记录属性提取具有1个关联记录和顺序的记录

我有一个Convos表，其中包含许多消息。

我想要的是:提取所有重要信息和最后一条消息。通过last_message.created_at订购convos

models.Convos.findAll({
    include: [
        {
            model: models.Messages,
            as: "last_message",
            order: [ [ 'created_at', 'DESC' ]],
            limit: 1,
         }
    ],
    where:{
        [Op.or]: [
            {
                sender_id: req.decoded.id
            },
            {
                recipient_id: req.decoded.id
            }
        ],
    },
)}

我最喜欢订购的是:

order: [
  [{model: models.Messages, as: 'last_message'}, 'created_at', 'DESC'],
],

但这给出了错误:

`Unhandled rejection SequelizeDatabaseError: missing FROM-clause entry for table "last_message"`

从这里开始，我猜测此错误可能暗示有convos，但没有任何消息，从而使last_message.created_at未定义(尽管我可能完全误解了此错误)。

因此，从那里开始，我一直试图在where语句中添加一个子句，该子句仅提取具有至少1条消息的convos。这是我尝试过的一堆东西，它们都抛出错误:

添加到哪里:

Sequelize.literal("`last_message`.`id` IS NOT NULL")


'$models.Messages.id$': { [Op.ne]: null },

Sequelize.fn("COUNT", Sequelize.col("last_message")): { [Op.gt]: 0 }
'$last_message.id$': { [Op.ne]: null }

'$last_message.id$': {
  [Op.ne]: null
}

我还尝试了having而不是where语句:

having: Sequelize.where(Sequelize.fn('COUNT', Sequelize.col('$last_message.id$')), '>=', 0)

如何根据相关记录last_message.created_at对convos进行正确排序？

更新-CONVOS模型的相关部分

"use strict";
module.exports = (sequelize, DataTypes) => {
  let Convos = sequelize.define(
    "Convos",
    {
      sender_id: {
        type: DataTypes.INTEGER,
        references: {
          model: "Users",
          key: "id"
        }
      },
      recipient_id: {
        type: DataTypes.INTEGER,
        references: {
          model: "Users",
          key: "id"
        }
      },
      created_at: DataTypes.DATE,
      updated_at: DataTypes.DATE
    },
    {
      timestamps: false,
      freezeTableName: true,
      schema: "public",
      tableName: "convos"
    }
  );

  Convos.associate = models => {
    Convos.hasMany(models.Messages, {
      as: "last_message",
      foreignKey: "convo_id",
      sourceKey: "id"
    });
  };

  return Convos;
};

更新
我已经知道相关模型具有limit时，问题是使用Sequelize.literal。例如，这有效:

models.Convos.findAll({
  include: [
  {
        model: models.Messages,
        as: "last_message",
        order: [ [ 'created_at', 'DESC' ]],
        //limit: 1,
        required: true,
        duplicating: false,
    },
],
where: {
  [Op.or]: [
      {
          sender_id: req.decoded.id
      },
      {
          recipient_id: req.decoded.id
      }
  ],
},
order: [[Sequelize.literal(`last_message.created_at`), 'DESC']],
offset: offset,
limit: 10,
}).then(convos => { ....

但是，当我取消注释include部分中的limit: 1时，出现错误:Unhandled rejection SequelizeDatabaseError: missing FROM-clause entry for table "last_message"
这是不含limit 1的查询日志:

Executing (default): SELECT "Convos"."id", "Convos"."sender_id", "Convos"."recipient_id", "Convos"."created_at", "Convos"."updated_at", "last_message"."id" AS "last_message.id", "last_message"."body" AS "last_message.body", "last_message"."read" AS "last_message.read", "last_message"."group_meeting_id" AS "last_message.group_meeting_id", "last_message"."user_id" AS "last_message.user_id", "last_message"."created_at" AS "last_message.created_at", "last_message"."updated_at" AS "last_message.updated_at", "last_message"."convo_id" AS "last_message.convo_id", "last_message->user"."id" AS "last_message.user.id", "last_message->user"."first_name" AS "last_message.user.first_name", "last_message->user"."avatar_file_name" AS "last_message.user.avatar_file_name", "senderUser"."id" AS "senderUser.id", "senderUser"."first_name" AS "senderUser.first_name", "senderUser"."avatar_file_name" AS "senderUser.avatar_file_name", "recipientUser"."id" AS "recipientUser.id", "recipientUser"."first_name" AS "recipientUser.first_name", "recipientUser"."avatar_file_name" AS "recipientUser.avatar_file_name" FROM "public"."convos" AS "Convos" INNER JOIN "public"."msgs" AS "last_message" ON "Convos"."id" = "last_message"."convo_id" LEFT OUTER JOIN "public"."users" AS "last_message->user" ON "last_message"."user_id" = "last_message->user"."id" LEFT OUTER JOIN "public"."users" AS "senderUser" ON "Convos"."sender_id" = "senderUser"."id" LEFT OUTER JOIN "public"."users" AS "recipientUser" ON "Convos"."recipient_id" = "recipientUser"."id" WHERE ("Convos"."sender_id" = 32 OR "Convos"."recipient_id" = 32) ORDER BY last_message.created_at DESC LIMIT 10 OFFSET 70;

用limit: 1查询:

Executing (default): SELECT "Convos"."id", "Convos"."sender_id", "Convos"."recipient_id", "Convos"."created_at", "Convos"."updated_at", "senderUser"."id" AS "senderUser.id", "senderUser"."first_name" AS "senderUser.first_name", "senderUser"."avatar_file_name" AS "senderUser.avatar_file_name", "recipientUser"."id" AS "recipientUser.id", "recipientUser"."first_name" AS "recipientUser.first_name", "recipientUser"."avatar_file_name" AS "recipientUser.avatar_file_name" FROM "public"."convos" AS "Convos" LEFT OUTER JOIN "public"."users" AS "senderUser" ON "Convos"."sender_id" = "senderUser"."id" LEFT OUTER JOIN "public"."users" AS "recipientUser" ON "Convos"."recipient_id" = "recipientUser"."id" WHERE ("Convos"."sender_id" = 32 OR "Convos"."recipient_id" = 32) ORDER BY last_message.created_at DESC LIMIT 10 OFFSET 0;

以下是一些有助于了解limit引起此问题的链接，但我仍未找到解决此问题的解决方案。

Link 1

Link 2

Link 3

Link 4

谢谢!

最佳答案

免责声明:我不知道续集。并且-有3个文档版本，您尚未说明使用的是哪个版本。



我可以提供一个SQL(Postgres)解决方案。

我假设有一张convos表(类似):

CREATE TABLE convos (
  id INT PRIMARY KEY,
  sender_id INT,
  recipient_id INT
);

和一个消息表

CREATE TABLE messages (
    id INT PRIMARY KEY,
    convo_id INT REFERENCES convos (id),
    created_at timestamp  without time zone
);

和一些测试数据:

INSERT INTO
convos (id, sender_id, recipient_id)
VALUES (1,1,1), (2,2,2), (3,3,4);

INSERT INTO
messages (id, convo_id, created_at)
VALUES
(1,1, '1990-07-24'),
(2,1, '2019-07-24'),
(3,2, '1990-07-24'),
(4,2, '2019-07-24'),
(5,3, null);

当您要查询convos表并从messages表中获取最新消息时，您将不得不在子查询(或CTE或横向联接，...)中使用messages的结果。

例如:

SELECT
    convos.sender_id,
    convos.recipient_id,
    messages.last_message_date
FROM convos
LEFT JOIN
(
    SELECT convo_id, max(created_at) as last_message_date
    FROM messages
    GROUP BY convo_id
) messages ON convos.id=messages.convo_id
ORDER BY  messages.last_message_date DESC

因此，对于sequelize.js-您必须找出它如何对关联模型进行子查询并使用结果。