Common Table Expressions and .count()

[christopherhesse]

Hey, thank for writing this great library!

I'm trying to use a CTE and .count() and it's not clear to me if this is supposed to work:

from peewee import *
import datetime

db = PostgresqlDatabase('peeweetest')

class BaseModel(Model):
    class Meta:
        database = db

# the user model specifies its fields (or columns) declaratively, like django
class User(BaseModel):
    username = CharField()
    password = CharField()
    email = CharField()
    join_date = DateTimeField()

db.create_tables([User])
User.create(username='username', password='password', email='email', join_date=datetime.datetime.now())

q1 = User.select()
print(list(q1.execute()))
print(q1.count())
cte = User.select().cte('users')
q2 = User.select().join(cte, on=(User.id == cte.c.id)).with_cte(cte)
print(list(q2.execute()))
print(q2.count())

The q2.count() query looks like

SELECT COUNT(1) FROM WITH "users" AS ((SELECT "t1"."id" FROM "user" AS "t1")) (SELECT 1 FROM "user" AS "t2" INNER JOIN "users" ON ("t2"."id" = "users"."id")) AS "_wrapped"

where I would have expected

WITH "users" AS ((SELECT "t1"."id" FROM "user" AS "t1")) SELECT COUNT(1) FROM (SELECT 1 FROM "user" AS "t2" INNER JOIN "users" ON ("t2"."id" = "users"."id")) AS "_wrapped"

pip show peewee
Name: peewee
Version: 3.7.1
Summary: a little orm

The docs do indeed say that .count() here will just wrap it in a COUNT thing, but I would have expected it to wrap more like my second query there. Am I calling count() wrong?

[coleifer]

Ahh, yeah, count() appears not to be taking into consideration the fact that the query has a CTE. This is a usage I hadn't considered.

I'd suggest just modifying your own SQL in the meantime as a workaround, e.g., to get the count, just write a query that looks like .select(fn.COUNT(...)).

[christopherhesse]

Thanks for the suggestion, but I first noticed this when using from playhouse.flask_utils import PaginatedQuery which seems to use .count() internally. Should I just change the source of that file to use this .select(fn.COUNT(...)) method?

[coleifer]

Ehh, or you could subclass PaginatedQuery and override the get_page_count() method?

[christopherhesse]

I ended up re-writing the query to not use CTEs. Do you have any interest in fixing CTEs with count()?

[coleifer]

I'm leaving the issue open for now as I believe there's a bug here. I just haven't figured out yet how to address it.

[coleifer]

A similar issue is reported by @iksteen in #1838.

Since these are closely related, I'm including the issue description here:

---

Consider the following example (note that this is obviously a silly example, but just enough to show the problem):

from peewee import *
db = PostgresqlDatabase('scoreboard')

class Base(Model):
    class Meta:
        database = db

class Challenge(Base):
    pass

class Attachment(Base):
    challenge = ForeignKeyField(Challenge, backref='attachments')

cte = (
    Challenge
    .select(fn.MAX(Challenge.id).alias('max_id'))
    .cte('demo')
)

q = (
    Challenge
    .select(Challenge)
    .join(cte, on=(Challenge.id == cte.c.min_id))
    .with_cte(cte)
)
p = Attachment.select()
r = prefetch(q, p)
print(list(r))

The generated SQL for the prefetch of Attachment will be:

SELECT "t1"."id", "t1"."challenge_id" FROM "attachment" AS "t1"
WHERE (
    "t1"."challenge_id" IN
    WITH "demo" AS ((SELECT MAX("t2"."id") AS "min_id" FROM "challenge" AS "t2"))
    (SELECT "t3"."id" FROM "challenge" AS "t3" INNER JOIN "demo" ON ("t3"."id" = "demo"."min_id"))
)

While the CTE queries and the subquery themselves do get extra parentheses, the base query is not parenthesized leaving the WITH part bare and resulting in an invalid syntax.

The expected query would be:

SELECT "t1"."id", "t1"."challenge_id" FROM "attachment" AS "t1"
WHERE (
    "t1"."challenge_id" IN (
        WITH "demo" AS (SELECT MAX("t2"."id") AS "min_id" FROM "challenge" AS "t2")
        SELECT "t3"."id" FROM "challenge" AS "t3" INNER JOIN "demo" ON ("t3"."id" = "demo"."min_id"))
    )
)

[coleifer]

Thanks to @iksteen for his fix in #1839. This is now resolved.

[christopherhesse]

Thanks @coleifer and @iksteen!