Issue: Common Table Expressions and .count()
Author: christopherhesse
State: closed
Created: 2018-12-10 23:35:29

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:

```py
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?

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(...))`.

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?

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

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

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.

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


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

Thanks @coleifer and @iksteen!