Peewee – Recursive CTEs – problem with the documentation example – OperationalError no such column: base.id

I have a problem with Peewee-3 and one of the tutorials in documentation: http://docs.peewee-orm.com/en/latest/peewee/querying.html#recursive-ctes

When I’m trying to run this code (nearly exact copy from doc) it’s rising an error:

Exception has occurred: OperationalError no such column: base.id

Here is my code (there is commented part with some testing categories):

_db = SqliteDatabase(DB_FILE)

class _Base(Model):
    class Meta:
        database = _db

class Category(_Base):
    name = CharField()
    parent = ForeignKeyField('self', backref='children', null=True)


# ADDING CATEGORIES
# _db.connect()
# _db.create_tables([Category])
# stocks = Category(name="stocks", parent=None)
# stocks.save()
# models = Category(name="models", parent=None)
# models.save()

# smoke = Category(name="smoke", parent=stocks)
# smoke.save()

# front = Category(name="front", parent=smoke)
# front.save()

# side = Category(name="side", parent=smoke)
# side.save()

# fluffy = Category(name="fluffy", parent=front)
# fluffy.save()
# _db.close()


Base = Category.alias()
level = Value(1).alias('level')
path = Base.name.alias('path')
base_case = (Base
             .select(Base.name, Base.parent, level, path)
             .where(Base.parent.is_null())
             .cte('base', recursive=True))

RTerm = Category.alias()
rlevel = (base_case.c.level + 1).alias('level')
rpath = base_case.c.path.concat('->').concat(RTerm.name).alias('path')
recursive = (RTerm
             .select(RTerm.name, RTerm.parent, rlevel, rpath)
             .join(base_case, on=(RTerm.parent == base_case.c.id)))

cte = base_case.union_all(recursive)

query = (cte
         .select_from(cte.c.name, cte.c.level, cte.c.path)
         .order_by(cte.c.path))

for category in query:
    print(category.name, category.level, category.path)

What am I doing wrong and how can I fix it, there is an mistake in the documentation?

Answer

Thanks for the issue report. The problem is I omitted to select the category “id” in the example from the docs. The fix is quite simple:

base_case = (Base
             .select(Base.id, Base.name, Base.parent, level, path)  # Add Base.id
             .where(Base.parent.is_null())
             .cte('base', recursive=True))

...
recursive = (RTerm
             .select(RTerm.id, RTerm.name, RTerm.parent, rlevel, rpath)  # Add RTerm.id
             .join(base_case, on=(RTerm.parent == base_case.c.id)))

I’ve updated the docs accordingly.