使用peewee創建子查詢，在子查詢結果上使用`.select`

我有一個相當復雜的 peewee 查詢，如下所示：

        SolutionAlias = Solution.alias()
        fields = [
            SolutionAlias.id.alias('solution_id'),
            SolutionAlias.solver.id.alias('solver_id'),
            SolutionAlias.exercise.id.alias('exercise_id'),
        ]

        query = (
            User
            .select(*fields)
            .join(Exercise, JOIN.CROSS)
            .join(Course, JOIN.LEFT_OUTER, on=(Exercise.course == Course.id))
            .join(SolutionAlias, JOIN.LEFT_OUTER, on=(
                (SolutionAlias.exercise == Exercise.id)
                & (SolutionAlias.solver == User.id)
            ))
            .where(
                (Exercise.id << self.get_exercise_ids()),
                (User.id << self.get_user_ids()),
            )
            .group_by(Exercise.id, User.id, SolutionAlias.id)
            .having(
                (SolutionAlias.id == fn.MAX(SolutionAlias.id))
                | (SolutionAlias.id.is_null(True))
            )
            .alias('solutions_subquery')
        )

        full_query_fields = [
            query.c.solver_id,
            query.c.exercise_id,
            Solution.id.alias('solution_id'),
            SolutionAssessment.icon.alias('assessment_icon'),
        ]

        solutions = (
            Solution
            .select(*full_query_fields)
            .join(query, JOIN.RIGHT_OUTER, on=(
                Solution.id == query.c.solution_id
            ))
            .join(SolutionAssessment, JOIN.LEFT_OUTER, on=(
                (Solution.assessment == SolutionAssessment.id)
            ))
        )

這個確實有效，生成以下 SQL 查詢：

SELECT 
  "solutions_subquery"."solver_id", 
  "solutions_subquery"."exercise_id", 
  "t1"."id" AS "solution_id",
  "t2"."icon" AS "assessment_icon" 
FROM 
  "solution" AS "t1" 
  RIGHT OUTER JOIN (
    SELECT 
      "t3"."id" AS "solution_id", 
      "t4"."id" AS "solver_id", 
      "t5"."id" AS "exercise_id" 
    FROM 
      "user" AS "t4" CROSS 
      JOIN "exercise" AS "t5" 
      LEFT OUTER JOIN "course" AS "t6" ON ("t5"."course_id" = "t6"."id") 
      LEFT OUTER JOIN "solution" AS "t3" ON (
        ("t3"."exercise_id" = "t5"."id") 
        AND ("t3"."solver_id" = "t4"."id")
      ) 
    WHERE 
      (
        (
          "t5"."id" IN (155, 156, 157)
        ) 
        AND (
          "t4"."id" IN (1, 24, 25, 26, 27, 28)
        )
      ) 
    GROUP BY 
      "t5"."id", 
      "t4"."id", 
      "t3"."id" 
    HAVING 
      (
        (
          "t3"."id" = MAX("t3"."id")
        ) 
        OR ("t3"."id" IS NULL)
      )
  ) AS "solutions_subquery" ON (
    "t1"."id" = "solutions_subquery"."solution_id"
  ) 
  LEFT OUTER JOIN "solutionassessment" AS "t2" ON ("t1"."assessment_id" = "t2"."id")

但我真的不想使用 RIGHT_JOIN，因為 SQLite 不支持它。

當使用子查詢試圖查詢query和JOIN荷蘭國際集團的Solution表到子查詢的結果，我從peewee錯誤。

新查詢：

        solutions = (
            query
            .select(*full_query_fields)
            .join(Solution, JOIN.LEFT_OUTER, on=(
                Solution.id == query.c.solution_id
            ))
            .join(SolutionAssessment, JOIN.LEFT_OUTER, on=(
                (Solution.assessment == SolutionAssessment.id)
            ))
        )

生成的查詢：

SELECT 
  "solutions_subquery"."solver_id", 
  "solutions_subquery"."exercise_id", 
  "t1"."id" AS "solution_id", 
  "t1"."checker_id", 
  "t1"."state", 
  "t1"."submission_timestamp", 
  "t2"."name" AS "assessment", 
  "t2"."icon" AS "assessment_icon" 
FROM 
  "user" AS "t3" CROSS 
  JOIN "exercise" AS "t4" 
  LEFT OUTER JOIN "course" AS "t5" ON ("t4"."course_id" = "t5"."id") 
  LEFT OUTER JOIN "solution" AS "t6" ON (
    ("t6"."exercise_id" = "t4"."id") 
    AND ("t6"."solver_id" = "t3"."id")
  ) 
  LEFT OUTER JOIN "solution" AS "t1" ON (
    "t1"."id" = "solutions_subquery"."solution_id"
  ) 
  LEFT OUTER JOIN "solutionassessment" AS "t2" ON ("t1"."assessment_id" = "t2"."id") 
WHERE 
  (
    (
      "t4"."id" IN (155, 156, 157)
    ) 
    AND (
      "t3"."id" IN (1, 24, 25, 26, 27, 28)
    )
  ) 
GROUP BY 
  "t4"."id", 
  "t3"."id", 
  "t6"."id" 
HAVING 
  (
    (
      "t6"."id" = MAX("t6"."id")
    ) 
    OR ("t6"."id" IS NULL)
  )

結果是：

psycopg2.errors.UndefinedTable: missing FROM-clause entry for table "solutions_subquery"
LINE 1: ...EFT OUTER JOIN "solution" AS "t1" ON ("t1"."id" = "solutions...

During handling of the above exception, another exception occurred:
[Truncated for readability...]

loguru.logger.critical(str(list(solutions.dicts().execute())))
[Truncated for readability...]

peewee.ProgrammingError: missing FROM-clause entry for table "solutions_subquery"
LINE 1: ...EFT OUTER JOIN "solution" AS "t1" ON ("t1"."id" = "solutions...

為什么 peewee 會展平查詢？還有其他使用方法LEFT_JOIN嗎？

uj5u.com熱心網友回復：

最終Select在檔案中找到了該函式，這使我可以對之前的查詢進行某種包裝：

        solutions = (
            Select(columns=full_query_fields)
            .from_(query)
            .join(Solution, JOIN.LEFT_OUTER, on=(
                Solution.id == query.c.solution_id
            ))
            .join(SolutionAssessment, JOIN.LEFT_OUTER, on=(
                (Solution.assessment == SolutionAssessment.id)
            ))
        )

此解決方案有效。

標籤：sql PostgreSQL的 sqlite 烧瓶 尿尿

上一篇：啟動時無法訪問虛擬環境中的包

下一篇：使用PythonFlaskPut請求更新字典