Sequelize关联表计算平均评分报错：missing FROM-clause entry for table 'ratings'

问题现象 #

执行Sequelize代码从关联表计算平均评分时，触发错误：

error: missing FROM-clause entry for table "ratings"

原代码 #

GigWorkers.findAll({
  limit,
  offset,
  where:{},
  attributes: {
    include: [ 
      [sequelize.fn('AVG', sequelize.col('ratings.rating')), 'avgRatings'],
    ],
  },
  include: [
    GigType, 
    GigWorkerMeta, 
    {
      model: ProfilePhoto,
      where:{
        type:"profile"
      }, 
      required:false,
    },
    {
      model: Documents,
      where:{
        type:"profile"
      }, 
      required:false,
    },
    {
      model:Referee,
      required:false,
    },
    {
      model:Ratings,
      as:"ratings",
      attributes: [],
      required:false,
    }
  ],
  group: ['gig_workers.id'],
  distinct: true,
  //required:false,
});

生成的错误SQL #

SELECT "gig_workers".*, "gig_types"."id" AS "gig_types.id", "gig_types"."name" AS "gig_types.name", "gig_types"."qualifications" AS "gig_types.qualifications", "gig_types"."experience" AS "gig_types.experience", "gig_types"."pay_rate" AS "gig_types.pay_rate", "gig_types"."dress_code" AS "gig_types.dress_code", "gig_types"."active" AS 
"gig_types.active", "gig_types"."createdAt" AS "gig_types.createdAt", "gig_types"."updatedAt" AS "gig_types.updatedAt", "gig_types->gig_worker_type_rel"."gigWorkerId" AS "gig_types.gig_worker_type_rel.gigWorkerId", "gig_types->gig_worker_type_rel"."gigTypeId" AS "gig_types.gig_worker_type_rel.gigTypeId", "gig_worker_metas"."id" AS "gig_worker_metas.id", "gig_worker_metas"."gig_worker_id" AS "gig_worker_metas.gig_worker_id", "gig_worker_metas"."key" AS "gig_worker_metas.key", "gig_worker_metas"."value" AS "gig_worker_metas.value", "gig_worker_metas"."createdAt" AS "gig_worker_metas.createdAt", "gig_worker_metas"."updatedAt" AS "gig_worker_metas.updatedAt", "profile_photos"."id" AS "profile_photos.id", "profile_photos"."user_id" AS "profile_photos.user_id", "profile_photos"."url" AS "profile_photos.url", "profile_photos"."type" AS "profile_photos.type", "profile_photos"."case_id" AS "profile_photos.case_id", "profile_photos"."createdAt" AS "profile_photos.createdAt", "profile_photos"."updatedAt" AS "profile_photos.updatedAt", "documents"."id" AS "documents.id", "documents"."user_id" AS "documents.user_id", "documents"."url" AS "documents.url", "documents"."type" AS "documents.type", "documents"."case_id" AS "documents.case_id", "documents"."createdAt" AS "documents.createdAt", "documents"."updatedAt" AS "documents.updatedAt", "referees"."id" AS "referees.id", "referees"."first_name" AS "referees.first_name", "referees"."last_name" AS "referees.last_name", "referees"."email" AS "referees.email", "referees"."phone" AS "referees.phone", "referees"."role" AS "referees.role", "referees"."job_venue" AS "referees.job_venue", "referees"."gig_worker_id" AS "referees.gig_worker_id", "referees"."assessment_given" 
AS "referees.assessment_given", "referees"."gig_worker_assessment" AS "referees.gig_worker_assessment", "referees"."score" AS "referees.score", "referees"."createdAt" AS "referees.createdAt", "referees"."updatedAt" AS "referees.updatedAt", "ratings"."id" AS "ratings.id", "ratings"."gig_id" AS "ratings.gig_id", "ratings"."business_id" AS "ratings.business_id", "ratings"."gig_worker_id" AS "ratings.gig_worker_id", "ratings"."type" AS "ratings.type", "ratings"."rating" AS "ratings.rating", "ratings"."evaluation" AS "ratings.evaluation", "ratings"."review" AS "ratings.review", "ratings"."assessment_given" AS "ratings.assessment_given", "ratings"."createdAt" AS "ratings.createdAt", "ratings"."updatedAt" AS "ratings.updatedAt" FROM (SELECT "gig_workers"."id", "gig_workers"."user_id", "gig_workers"."first_name", "gig_workers"."last_name", "gig_workers"."address", "gig_workers"."contact_number", "gig_workers"."profile_picture", "gig_workers"."work_rights", "gig_workers"."status", "gig_workers"."verified", "gig_workers"."available_time", "gig_workers"."license", "gig_workers"."venue_experience", "gig_workers"."certification", "gig_workers"."language", "gig_workers"."latitude", "gig_workers"."longitude", "gig_workers"."radius", "gig_workers"."createdAt", "gig_workers"."updatedAt", 
AVG("ratings"."rating") AS "avgRatings" FROM "gig_workers" AS "gig_workers" GROUP BY "gig_workers"."id" LIMIT 30 OFFSET 0) AS "gig_workers"
LEFT OUTER JOIN ( "gig_worker_type_rels" AS "gig_types->gig_worker_type_rel" INNER JOIN "gig_types" AS "gig_types" ON "gig_types"."id" = "gig_types->gig_worker_type_rel"."gigTypeId") ON "gig_workers"."id" = "gig_types->gig_worker_type_rel"."gigWorkerId" 
LEFT OUTER JOIN "gig_worker_metas" AS "gig_worker_metas" ON "gig_workers"."id" = "gig_worker_metas"."gig_worker_id" 
LEFT OUTER JOIN "profile_photos" AS "profile_photos" ON "gig_workers"."id" = "profile_photos"."case_id" AND "profile_photos"."type" = 'profile' 
LEFT OUTER JOIN "documents" AS "documents" ON "gig_workers"."id" = "documents"."case_id" AND "documents"."type" = 'profile' 
LEFT OUTER JOIN "referees" AS "referees" ON "gig_workers"."id" = "referees"."gig_worker_id" 
LEFT OUTER JOIN "ratings" AS "ratings" ON "gig_workers"."id" = "ratings"."gig_worker_id";

问题原因 #

生成的SQL中，计算AVG("ratings"."rating")的子查询未关联ratings表，ratings仅在外部主查询通过LEFT JOIN关联，子查询无法访问外部表，因此触发"missing FROM-clause entry"错误。同时distinct: true与group混用会导致Sequelize生成嵌套子查询，进一步放大问题。

修复方案 #

方案一：子查询直接计算平均评分（推荐）

移除include中的Ratings关联，改用子查询在attributes中计算平均，无需关联整个表，性能更优：

GigWorkers.findAll({
  limit,
  offset,
  where:{},
  attributes: {
    include: [ 
      [
        sequelize.literal(`(SELECT AVG(rating) FROM ratings WHERE ratings.gig_worker_id = gig_workers.id)`), 
        'avgRatings'
      ],
    ],
  },
  include: [
    GigType, 
    GigWorkerMeta, 
    {
      model: ProfilePhoto,
      where:{
        type:"profile"
      }, 
      required:false,
    },
    {
      model: Documents,
      where:{
        type:"profile"
      }, 
      required:false,
    },
    {
      model:Referee,
      required:false,
    }
  ],
  distinct: true,
});

方案二：调整关联方式，确保子查询包含ratings表

若需保留Ratings关联，可移除distinct: true，并在Ratings的include配置中添加duplicating: false，让Sequelize将ratings关联放入子查询：

GigWorkers.findAll({
  limit,
  offset,
  where:{},
  attributes: {
    include: [ 
      [sequelize.fn('AVG', sequelize.col('ratings.rating')), 'avgRatings'],
    ],
  },
  include: [
    GigType, 
    GigWorkerMeta, 
    {
      model: ProfilePhoto,
      where:{
        type:"profile"
      }, 
      required:false,
    },
    {
      model: Documents,
      where:{
        type:"profile"
      }, 
      required:false,
    },
    {
      model:Referee,
      required:false,
    },
    {
      model:Ratings,
      as:"ratings",
      attributes: [],
      required:false,
      duplicating: false // 关键配置，避免重复关联
    }
  ],
  group: ['gig_workers.id'],
});

说明 #

方案一适合仅需平均评分的场景，查询效率更高；方案二则适合同步获取ratings其他数据的需求。

内容的提问来源于stack exchange，提问作者Md.Shakil Shaikh