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, > > 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] <javascript:>. > To post to this group, send email to [email protected] > <javascript:>. > 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.
