On 8/27/15 2:27 PM, [email protected] wrote:
Having an adjacency list tree (actually, in my case it's a DAG) model
it's easy to write a recursive CTE which returns a list of the nodes
together with their level or path from the root, and applying filters
is also no problem. However, I couldn't find in the documentation and
previous discussions any information about how to fill a proper
hierarchy this way? I mean a kind of eager loading, like
root.children[i].children[i]..., with arbitrary number of levels. All
the data required to build this hierarchy is present in the CTE
results, so it should be possible.
yes so, you need to piece together the structure manually.
e.g.:
all_objects = session.query(Node).filter(...CTE
stuff...).order_by(Node.parent_id, Node.id).all()
then the rest I cut-and-paste more or less from
https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/DisjointEagerLoading,
I'm using groupby here but that relies on the results being ordered by
parent_id, and also assumes every node is only a member of exactly one,
or zero, collections. If it's more complicated than that in your
structure, just maintain a dictionary of all collections and build them
all up keyed to Node.id as they need to be:
from itertools import groupby
children = dict(
(k, list(v)) for k, v in groupby(
all_objects, lambda node: node.parent_id
)
)
then you connect the collections to their objects in one step like this:
from sqlalchemy.orm import attributes
for node in all_objects:
attributes.set_committed_value(node, "children",
children.get(node.id, ()))
Of course, in a simple case I could just use joinedload with the depth
equal to the longest path length, but adding some filtering requires
explicit aliased joins with contains_eager which can greatly increase
the code complexity: in my case I have many-to-many relationships as
the structure is a DAG (not a tree), and I also need to load two more
relationship (like root.children[i].child.some_properties) which I
join (and alias, and add contains_eager for it) for each level.
What's the preferred solution for such problems?
--
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 [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at http://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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
