Peewee 3 - Order by and Recursive common table expression (cte)

Question

Tools: Peewee 3, SQLite, Python 3

Official documentation for Peewee 3 recursive common table expression (cte):
http://docs.peewee-orm.com/en/latest/peewee/querying.html#common-table-expressions

I am storing a family tree in a simple self-referencing table called Person.
Structure (see below): id, name, parent, custom_order

Notes:
- parent field equals null if this person is an ancestor / root item, otherwise equals to id of parent record if this person is a child
- custom_order is a float number (score to determine who is the user's favourite person)

Objective:
I would like to retrieve the whole family tree and ORDER the results FIRST by parent and SECOND by custom_order.

Issue:
I managed to get the results list but the ORDER is wrong.

DB model

class Person(Model):
    name = CharField()
    parent = ForeignKeyField('self', backref='children', null = True) 
    custom_order = FloatField()

Note: if parent field is null then it's a root item

Query code

# Define the base case of our recursive CTE. This will be people that have a null parent foreign-key.
Base = Person.alias()
base_case = (Base
    .select(Base)
    .where(Base.parent.is_null())
    .cte('base', recursive=True))

# Define the recursive terms.
RTerm = Person.alias()
recursive = (RTerm
    .select(RTerm)
    .join(base_case, on=(RTerm.parent == base_case.c.id)))

# The recursive CTE is created by taking the base case and UNION ALL with the recursive term.
cte = base_case.union_all(recursive)

# We will now query from the CTE to get the people
query = cte.select_from(cte.c.id, cte.c.name, cte.c.parent_id, cte.c.custom_order).order_by(cte.c.parent_id, cte.c.custom_order)

print(query.sql())

Printed query syntax

('WITH RECURSIVE "base" AS 
(
SELECT "t1"."id", "t1"."name", "t1"."parent_id", "t1"."custom_order" FROM "person" AS "t1" WHERE ("t1"."parent_id" IS ?) 
UNION ALL 
SELECT "t2"."id", "t2"."name", "t2"."parent_id", "t2"."custom_order" FROM "person" AS "t2" INNER JOIN "base" ON ("t2"."parent_id" = "base"."id")
) 
SELECT "base"."id", "base"."name", "base"."parent_id" FROM "base" 
ORDER BY "base"."parent_id", "base"."custom_order"', 
[None])

score -1 · Accepted Answer · answered Dec 08 '19 at 13:07

Root of the problem
The code posted in the question works correctly. I verified it by printing the query results to the console:

query = cte.select_from(cte.c.id, cte.c.name, cte.c.parent_id, cte.c.custom_order).order_by(cte.c.parent_id, cte.c.custom_order).dicts()
print(json.dumps(list(query), indent=4))

The problem originated from the fact that I was passing the query results to a nested python Dictionary before printing them to the console, BUT the Python Dictionary is unordered. So, no wonder the printed results were in a different order than the database results.

Solution
Use a Python Ordered Dictionary if you want to store the query results in a fixed order:

import collections
treeDictionary = collections.OrderedDict()

Peewee 3 - Order by and Recursive common table expression (cte)

1 Answers1