On 06/09/2016 05:39 AM, Adrian wrote:
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...
"name" is optional, it will generate an anoymous name if omitted. I'm
not sure what the problem is here but perhaps it will get a little
further that way and you'd be able to see what the problem is.
otherwise I'd need an MCVE for this in order to play with it. CTEs are
painful.
--
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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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.
