Find place number in queue, peewee producing an illegal sqlite3 query

Question

I'm trying to find the place number of a ticket in a queue. The query fails at sqlite3, and the error given is quite peculiar: sqlite3.OperationalError: near "(": syntax error. I don't seem to understand how the column references are used.

I've tried separating into more subqueries, looked at countless examples of aggregate use, window functions, etc. in peewee, sqlite and MySQL. What I haven't found is an example of fn.ROW_NUMBER in peewee. I checked the query on multiple websites, and they would parrot the same error.

Query generated by peewee:

SELECT ROW_NUMBER() OVER (ORDER BY "t1"."ordinal") AS ordinal_index 
FROM "dbticket" AS t1 
WHERE (((("t1"."called_at" IS ?) AND ("t1"."abandoned_at" IS ?)) AND ("t1"."removed_at" IS ?)) AND ("ticket_id" = ?))

Parameters:

[None, None, None, 'red-shibe'

The code responsible for this hell:

from pathlib import Path
import os
THIS_FOLDER = Path(os.path.dirname(os.path.abspath(__file__)))
db_file = THIS_FOLDER / 'queue.db'
sqlite_db = SqliteDatabase(str(db_file), pragmas=[('journal_mode', 'wal')])



class DBTicket(BaseModel):
    ordinal = PrimaryKeyAutoIncrementField()
    ticket_id = TextField(unique=True)
    queue_name = TextField()

    initiated_at = DateTimeField(default=datetime.utcnow)
    called_at = DateTimeField(null=True)
    abandoned_at = DateTimeField(null=True)
    removed_at = DateTimeField(null=True)

    def __str__(self):
        return f'{self.ordinal}:{self.ticket_id}'

in_queue_predicate = (DBTicket.called_at.is_null() &
                      DBTicket.abandoned_at.is_null() &
                      DBTicket.removed_at.is_null())

def ticket_place_number(ticket_id):
    queue = DBTicket.select() \
                    .where(in_queue_predicate)
    place_number = queue.select(fn.ROW_NUMBER().over(order_by=[DBTicket.ordinal]).alias('ordinal_index')) \
                        .where(queue.c.ticket_id == ticket_id) \
                        .scalar()
    return place_number

Answer 1

I don't see any immediate issues, other than that you might check your sqlite version. Window functions are a relatively recent addition and you need 3.25.0 or newer.

import sqlite3
sqlite3.sqlite_version