Create subquery using peewee, using `.select` on the subquery results

I have a rather complex peewee query that looks like that:

        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)
            ))
        )

This one actually works, generating the following SQL query:

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")

But I don’t really want to use RIGHT_JOIN as it isn’t supported by SQLite.

When trying to query using the subquery query and JOINing the Solution table into the subquery’s result, I get an error from peewee.

The new query:

        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)
            ))
        )

The generated query:

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)
  )

Which results in:

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...

Why does peewee flatten the query? Is there another way to use LEFT_JOIN?

Answer

Eventually found the Select function in the documentation, which allows me to kind of wrap the previous query:

        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)
            ))
        )

This solution works.

Advertisement

Answer