I'm trying to add a `deep_children_count` column property to one of my
models.
As a regular property it works perfectly fine but I'd like to make it a
column property so I don't have to spam extra queries if I need the counts
for multiple objects.
So I tried this:
cat_alias = db.aliased(Category)
cte_query = (select([cat_alias.id, db.cast(array([]), ARRAY(db.Integer
)).label('parents')])
.where(cat_alias.parent_id.is_(None) & ~cat_alias.
is_deleted)
.cte('chains', recursive=True))
parent_query = (select([cat_alias.id, cte_query.c.parents.op('||')(
cat_alias.parent_id)])
.where((cat_alias.parent_id == cte_query.c.id) & ~
cat_alias.is_deleted))
cte_query = cte_query.union_all(parent_query)
query = select([db.func.count()]).where(cte_query.c.parents.contains(
array([Category.id])))
Category.deep_children_count = column_property(query)
Unfortunately this fails with an exception when loading one of the objects:
CompileError: Multiple, unrelated CTEs found with the same name: u'chains'
I'm not sure why I end up with *multiple* CTEs and since it's a compile
error I cannot look at the SQL it
tried to generate either...
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
