PostgreSQL报错：缺少ratings表的FROM子句条目求助

错误信息 #

ERROR: 缺少表"ratings"的FROM子句条目 LINE 4:
AVG("ratings"."rating") AS "avgRatings" FROM "gig_workers" A...
^ SQL状态: 42P01
字符位置: 3366

错误原因 #

子查询中尝试计算AVG("ratings"."rating")，但该子查询的FROM子句仅声明了gig_workers表，未关联ratings表，导致PostgreSQL无法找到ratings表的数据源，触发未定义表的错误。

修正方案 #

在子查询中通过LEFT JOIN关联ratings表（保证无评分的工人也能被查询到），再执行平均评分计算，同时保持按gig_workers.id分组的逻辑。

修正后的完整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"
   LEFT JOIN "ratings" AS "ratings" ON "gig_workers"."id" = "ratings"."gig_worker_id"
   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";

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