you would first set up a traditional adjacency list relationship as in
http://docs.sqlalchemy.org/en/latest/orm/self_referential.html?highlight=adjacency%20list.
Second, as you run this query you can retrieve Category objects fully,
however they will be in a straight down list. As you receive them,
assemble them into the tree structure you want using dictionaries
against target collections. To populate a relationship-related
collection at load time, use the set_committed_value() function passing
a completed collection.
demo:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
a_id = Column(Integer, ForeignKey('a.id'))
child_as = relationship("A")
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
s = Session(e)
s.add_all([
A(id=1, a_id=None),
A(id=2, a_id=1),
A(id=3, a_id=1),
A(id=4, a_id=1),
A(id=5, a_id=2),
A(id=6, a_id=2),
A(id=7, a_id=6),
A(id=8, a_id=7),
])
s.commit()
from collections import defaultdict
from sqlalchemy.orm.attributes import set_committed_value
collections = defaultdict(list)
objects = {}
for a_obj in s.query(A):
objects[a_obj.id] = a_obj
if a_obj.a_id is not None:
collections[a_obj.a_id].append(a_obj)
for id_, collection in collections.items():
set_committed_value(
objects[id_],
"child_as",
collection
)
On 06/03/2016 10:40 AM, Adrian wrote:
I have a Category model that has (among other things) a `id` and
`parent_id` since my categories are organized in a tree.
|
@property
defchain_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)
returnCategory.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
<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.
