时间戳

首页 > 解决方案 > 如何在 sequelize 中编写此 Raw Postgres 查询

sql - 如何在 sequelize 中编写此 Raw Postgres 查询

问题描述

我有这个 postgres RAW 查询，我想用 Sequelize 编写它。我该怎么做，因为我对在 Sequelize 中编写具有 JOINS 的查询不太了解。我制作了模型和关联。

这些是模型和关联。

TestParticipant.hasMany(ParticipantHistory, {
    sourceKey: "id",
    foreignKey: "participantId",
    as: "paticipantStatuses"
})

ParticipantHistory.belongsTo(TestParticipant, {
    foreignKey: "participantId",
    as: "paticipantStatuses"
})

这是我想转换为 Sequelize 查询的原始查询

SELECT participant_histories.participant_id,
        participant_histories.created_at,participant_histories.previous_status,
        participant_histories.status,test_participants.test_type_id,test_participants.id,
        test_participants.email,test_participants.scheduled_at,test_participants.valid_till,
        test_participants.is_proctored 
FROM test_participants 
  INNER JOIN participant_histories ON test_participants.id=participant_histories.participant_id 
WHERE user_id='${userId}' 
AND participant_histories.status='${activity}' 
AND participant_histories.created_at>='${isoDate}'

标签： sqlnode.jspostgresqlsequelize.js

解决方案

因为我在帖子中没有看到模型定义，所以我只建议这样的东西：

// First of all you should correct an alias for TestParticipant like this
ParticipantHistory.belongsTo(TestParticipant, {
    foreignKey: "participantId",
    as: "paticipant"
})

const rows = await ParticipantHistory.findAll({
  raw: true,
  attributes: ['participant_id', 'created_at', 'previous_status', 'status'],
  where: {
    status: activity,
    created_at: {
      [Op.gte]: isoDate
    }
  },
  include: [{
    required: true // this turns into INNER JOIN
    model: TestParticipant,
    attributes: ['test_type_id', 'id', 'email', 'scheduled_at', 'valid_till', 'is_proctored'],
    as: 'participant',
    where: {
      user_id: userId
    }
  }]
})

推荐阅读