Decipher Peewee ORM Postgres query error

Question

I'm using Peewee in my Flask project. I have an ORM query which worked in SQLite, but is now causing an error as I'm migrating to Postgres. I wondered if anyone could help me diagnose it:

stats = (
    Assignment.select(
        Type.name,
        fn.COUNT(Type.id).alias('total'),
        Assignment.select(
            fn.COUNT(Assignment.id)
        ).where(
            (Assignment.due_date < fn.Now()) & (StudentCourses.student == self) & (Assignment.type == Type.id)
        ).group_by(Assignment.type, StudentCourses.student).alias('completed')
    )
    .naive().join(Type)
    .join(StudentCourses, on=(StudentCourses.course == Assignment.course))
    .where(StudentCourses.student == self)
    .order_by(Type.id)
    .group_by(Type.name, StudentCourses.student)
)

Here's the resulting SQL:

SELECT "t2"."name",
       COUNT("t2"."id") AS total,
  (SELECT COUNT("t4"."id")
   FROM "assignment" AS t4
   WHERE ((("t4"."due_date" < Now())
           AND ("t3"."student_id" = 2))
          AND ("t4"."type_id" = "t2"."id"))
   GROUP BY "t4"."type_id",
            "t3"."student_id") AS completed
FROM "assignment" AS t1
INNER JOIN "type" AS t2 ON ("t1"."type_id" = "t2"."id")
INNER JOIN "studentcourses" AS t3 ON ("t3"."course_id" = "t1"."course_id")
WHERE ("t3"."student_id" = 2)
GROUP BY "t2"."name",
         "t3"."student_id"
ORDER BY "t2"."id"

And here's the error I'm getting:

ProgrammingError: subquery uses ungrouped column "t2.id" from outer query
LINE 1: ...ND ("t3"."student_id" = 2)) AND ("t4"."type_id" = "t2"."id")...

I've tried adding the indicated columns to the group_by clause but I'm not getting any love. Does anyone have any guidance on this? Would love a fresh pair of eyes.

Answer 1

Your subquery is effectively joining on the outer query's Type table, so postgres is saying you need to group by Type.id in the COUNT subquery.