If there were some really nifty and totally extensible way to do it,
maybe, though the existing attributes.set_committed_value() hook was
added to make this sort of thing even possible at all.
On 8/27/15 3:19 PM, [email protected] wrote:
Thanks for this solution! And are there any plans for sqlalchemy to
support such cases in some general sense?
четверг, 27 августа 2015 г., 21:48:09 UTC+3 пользователь Michael Bayer
написал:
On 8/27/15 2:27 PM, [email protected] <javascript:> 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
<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 <http://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] <javascript:>.
To post to this group, send email to [email protected]
<javascript:>.
Visit this group at http://groups.google.com/group/sqlalchemy
<http://groups.google.com/group/sqlalchemy>.
For more options, visit https://groups.google.com/d/optout
<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]
<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.
