GROUP BY field by alias. exists() method exception

[TerehinAV]

Origin SQL query

SELECT public.user.id, public.user.telegram_id, public.user.first_name, public.user.last_name, public.user.username, MAX(receipt.receipt_date) as last_date, COUNT(DISTINCT DATE(receipt.receipt_date)) as days_with_receipts FROM public.user INNER JOIN receipt ON public.user.id = receipt.user_id WHERE receipt.receipt_date >= NOW() - INTERVAL '30 DAY' GROUP BY public.user.id, public.user.telegram_id, public.user.first_name, public.user.last_name, public.user.username ORDER BY days_with_receipts DESC, last_date DESC;

Peewee code

query = (User .select(User.id, User.telegram_id, User.first_name, User.last_name, User.username, pw.fn.MAX(Receipt.receipt_date).alias('last_date'), pw.fn.COUNT(pw.fn.DISTINCT(pw.fn.DATE(Receipt.receipt_date))).alias( 'days_with_receipts')) .join(Receipt, on=(User.id == Receipt.user_id)) .where(Receipt.receipt_date >= pw.fn.NOW() - pw.SQL("INTERVAL '30 DAY'")) .group_by(User.id, User.telegram_id, User.first_name, User.last_name, User.username) .order_by(pw.SQL('days_with_receipts').desc(), pw.SQL('last_date').desc()))

Peewee generated query

SELECT "t1"."id", "t1"."telegram_id", "t1"."first_name", "t1"."last_name", "t1"."username", MAX("t2"."receipt_date") AS "last_date", COUNT(DISTINCT(DATE("t2"."receipt_date"))) AS "days_with_receipts" FROM "user" AS "t1" INNER JOIN "receipt" AS "t2" ON ("t1"."id" = "t2"."user_id") WHERE ("t2"."receipt_date" >= (NOW() - INTERVAL '30 DAY')) GROUP BY "t1"."id", "t1"."telegram_id", "t1"."first_name", "t1"."last_name", "t1"."username" ORDER BY days_with_receipts DESC, last_date DESC;

If I call query.exists(), than it raises an exception with text:

peewee.ProgrammingError: column "days_with_receipts" does not exist LINE 1: ...name", "t1"."last_name", "t1"."username" ORDER BY dayswith...

This is because exists() generates such query to test record's existance:

SELECT 1 FROM "user" AS "t1" INNER JOIN "receipt" AS "t2" ON ("t1"."id" = "t2"."user_id") WHERE ("t2"."receipt_date" >= (NOW() - INTERVAL '30 DAY')) GROUP BY "t1"."id", "t1"."telegram_id", "t1"."first_name", "t1"."last_name", "t1"."username" ORDER BY days_with_receipts DESC, last_date DESC LIMIT

And it is true, in this case there is no such field.

Please give me a hint how to avoid or fix it.

python version 3.9 peewee version 3.14.8

[coleifer]

We would need to strip out the order by in order for that to work. I'd suggest instead of using .exists() to check .count() (Peewee will wrap the inner query so this problem is avoided). Alternatively, you can clean up your code and simplify the exists part -- I'm not sure that even calling .exists() is sensible here?

# Strip out the order by
query = (User
      .select(
        User.id, User.telegram_id, User.first_name, User.last_name, User.username,
        pw.fn.MAX(Receipt.receipt_date).alias('last_date'),
        pw.fn.COUNT(pw.fn.DISTINCT(pw.fn.DATE(Receipt.receipt_date))).alias(
'days_with_receipts'))
      .join(Receipt, on=(User.id == Receipt.user_id))
      .where(Receipt.receipt_date >= pw.fn.NOW() - pw.SQL("INTERVAL '30 DAY'"))
      .group_by(User.id, User.telegram_id, User.first_name, User.last_name, User.username)
      .order_by(pw.SQL('days_with_receipts').desc(), pw.SQL('last_date').desc()))
if query.order_by().exists():
    # ...

# OR - use .count()
query = (User
      .select(
        User.id, User.telegram_id, User.first_name, User.last_name, User.username,
        pw.fn.MAX(Receipt.receipt_date).alias('last_date'),
        pw.fn.COUNT(pw.fn.DISTINCT(pw.fn.DATE(Receipt.receipt_date))).alias(
'days_with_receipts'))
      .join(Receipt, on=(User.id == Receipt.user_id))
      .where(Receipt.receipt_date >= pw.fn.NOW() - pw.SQL("INTERVAL '30 DAY'"))
      .group_by(User.id, User.telegram_id, User.first_name, User.last_name, User.username)
      .order_by(pw.SQL('days_with_receipts').desc(), pw.SQL('last_date').desc()))
if query.count():
    ...

# OR simplify
query = (User
      .select()
      .join(Receipt, on=(User.id == Receipt.user_id))
      .where(Receipt.receipt_date >= pw.fn.NOW() - pw.SQL("INTERVAL '30 DAY'")))
if query.exists():
    ... do the full query here now.