求助：将PostgreSQL查询转换为Sequelize及解决关联配置问题

Hey there! Let's work through converting your PostgreSQL query into working Sequelize code, and also fix up the model association setup since you mentioned having issues there.

First, let's make sure your models are properly associated to match the JOIN logic in your query. Assuming you have three models: Actor, ActorStatus, and Movie, here's how to set up their relationships:

Step 1: Configure Model Associations #

// In Actor model
Actor.hasMany(ActorStatus, {
  foreignKey: 'actor_id',
  as: 'actorStatuses' // Matches the quoted table name in your query
});

// In ActorStatus model
ActorStatus.belongsTo(Actor, {
  foreignKey: 'actor_id'
});

// Note: Your query uses `movies.id = "actorStatuses".actor_id` which is unusual
// Normally an actor status would link to a movie via a `movie_id` column, but we'll follow your query logic here
ActorStatus.belongsTo(Movie, {
  foreignKey: 'actor_id',
  targetKey: 'id',
  as: 'movie'
});

Step 2: Write the Sequelize Query #

Now we can translate your SELECT * with JOINs into a Sequelize findAll call. We'll use nested include clauses to replicate the JOINs, and apply the date filter on the Movie model:

const matchingRecords = await Actor.findAll({
  include: [
    {
      model: ActorStatus,
      as: 'actorStatuses',
      include: [
        {
          model: Movie,
          as: 'movie',
          where: {
            // Use ISO date format (YYYY-MM-DD) for PostgreSQL to avoid parsing issues
            date: '2017-07-08'
          }
        }
      ]
    }
  ],
  // By default, Sequelize returns model instances. If you want raw database objects, add:
  // raw: true
});

Important Notes #

• Date Format: PostgreSQL prefers ISO 8601 date formats (YYYY-MM-DD) over MM/DD/YYYY. Using the ISO format will prevent unexpected date parsing errors.
• Unusual Association: Your original query joins movies to actorStatuses using actor_id, which isn't a typical relationship (usually you'd have a movie_id column in actorStatuses linking to movies.id). If this was a typo, simply update the foreignKey in the ActorStatus.belongsTo(Movie) association to movie_id instead of actor_id.

内容的提问来源于stack exchange，提问作者spaceDog