I have a Category model that has (among other things) a `id` and
`parent_id` since my categories are organized in a tree.
@property
def chain_query(self):
"""Get a query object for the category chain.
The query retrieves the root category first and then all the
intermediate categories up to (and including) this category.
"""
cte_query = (select([Category.id, Category.parent_id, literal(0).
label('level')])
.where(Category.id == self.id)
.cte('category_chain', recursive=True))
parent_query = (select([Category.id, Category.parent_id, cte_query.c
.level + 1])
.where(Category.id == cte_query.c.parent_id))
cte_query = cte_query.union_all(parent_query)
return Category.query.join(cte_query, Category.id == cte_query.c.id
).order_by(cte_query.c.level.desc())
This works fine, but I'd now I'd like to fetch multiple categories and get
their parent chains too as if they were in a relationship.
Is there any way to adapt what I currently have using either `relationship`
(and probably some magic to use the Category model but fetch from the CTE)
or `column_property` to achieve this?
--
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.
