postgresql - 如何在 sequelize PostgreSQL 中根据用户 ID 对数据进行分组？

Question

我想将一些用户的所有消息分组到一个数组中。我怎样才能做到这一点？我有近 200 个用户，我想在单独的数组中显示每个用户的对话以及用户详细信息。

chat table has recipientId, senderId message, created_at & updated_at.

我试过这个，但它返回消息以及发件人的详细信息:

models.chat.findAll({ include: [
      {
        model:models.User,
        as: 'User'
      }
    ],
    order: [['senderId ']],
});

期望的输出:

[
  {
    "id": 5,
    "username": "username",
    "email": "username@gmail.com.com",
    "password": "o15G8DdMqRE0Ksm",
    "created_at": "2018-12-07T05:46:05.590Z",
    "updated_at": "2018-12-07T05:46:05.590Z"
    "messages":
      [
        {
          "id": 5,
          "message": "hi",
          "senderId ": 1,
          "recipientId": 5,
          "created_at": "2018-12-07T05:53:48.229Z",
          "updated_at": "2018-12-07T05:53:48.229Z",
        },
        { 
          "id": 7,
          "message": "how are you",
          "senderId ": 5,
          "recipientId": 1,
          "created_at": "2018-12-07T05:53:48.229Z",
          "updated_at": "2018-12-07T05:53:48.229Z"
        },
        {...},
        {...}
      ]  
  },
]

聊天模式:

 chat.associate = (models) => {
    chat.belongsTo(models.User, {
      foreignKey: 'senderId',
      onDelete: 'CASCADE'
    });
    chat.belongsTo(models.User, {
      foreignKey: 'recipientId',
      onDelete: 'CASCADE'
    });
  };

用户模型:

      const User = sequelize.define('User', {
    username: {
      type: DataTypes.STRING
    },
    email: {
      type: DataTypes.STRING,
      allowNull: false,
      unique: true
    },
    password: {
      type: DataTypes.STRING,
      allowNull: false
    }
  }, {
    underscored: true,
    freezeTableName: true,
    tableName: 'users',
    hooks: {
      beforeCreate: beforeCreate,
      beforeBulkUpdate: beforeBulkUpdate
    }
  });
 User.associate = (models) => {
User.hasMany(models.Message, {
  foreignKey: 'fromUserId',
  as: 'messages'
});
User.hasMany(models.Message, {
  foreignKey: 'toUserId',
  as: 'messagess'
});
};

Answer 1

您可以通过以下方式获得所需的输出:

models.User.findAll({ 
    where : { id : USER_ID },
    include: [
        {
            model:models.chat,
            required : false ,
            where : {
                $or : [ { senderId : USER_ID } , { recipientId : USER_ID }]
            }
        }
    ]
});

NOTE :

You can check by removing required : false , as I have not tested the code.