CODEWITHSUNDEEP
python-3.xpeewee
Igor Carmagna
Igor Carmagna

Reputation: 957

Peewee 3 - Python - Rank query results

I have a self-referencing db table with notes (id, title, parent).
[Note: the self-referencing property of the table is not relevant to the question]
I would like to order them alphabetically by title and find the rank (= order) of a specific note by its id. I'm using Peewee 3 as ORM.

DB Model: 

class Note(Model):
    title = CharField()
    parent = ForeignKeyField('self', backref='children', null = True)

Code:

noteAlias = Note.alias()
subquery = (noteAlias.select(noteAlias.id, fn.RANK().over(partition_by=[noteAlias.title], order_by=[noteAlias.title]).alias('rank')).where(noteAlias.parent.is_null()).alias('subq'))
query = (Note.select(subquery.c.id, subquery.c.rank).from_(subquery).where(subquery.c.id == 5))
print("Rank of element is: " + str(query.rank))

This code gives me the following error: 

cursor.execute(sql, params or ())
sqlite3.OperationalError: near "(": syntax error

SQLite test
If I simply run this sqlite code directly against my db:

SELECT (title, ROW_NUMBER() OVER (ORDER BY title) AS placement) FROM note

I get the error: near ",": syntax error:

Upvotes: 0

Views: 172

Answers (1)

coleifer
coleifer

Reputation: 26245

Your version of SQLite perhaps doesn't support window functions, as this was added relatively recently in 3.25 I believe.

Upvotes: 0

Related Questions